ACCESS FEED: RE: [MS_AccessPros] Query not working properly

Lon-

Let me restate my question:

When you enter a "new" record for March 28 or later, which table is
affected?

My point is that if there are no matching records (because of the INNER
joins) in any one of the other tables, the record for March 28 won't show
up. If you run the query directly in the database, do you see records past
March 28?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon Lasher
Sent: Friday, March 29, 2013 9:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Query not working properly

I don't query on date at all. Sorry if that was misunderstood. Date does
not matter in this process. I query on Customer ID only. My issue is that
any record started prior to March 28 is accessible. Any NEW record entered
after March 28 does not respond to the query.
To: MS_Access_Professionals@yahoogroups.com
From: JohnV@msn.com
Date: Fri, 29 Mar 2013 21:29:37 +0100
Subject: RE: [MS_AccessPros] Query not working properly

Lon-

Which of the tables have fields with dates after March 28? I suspect

because of the INNER JOINs that there are no matching records for that date

in the other tables, so you get no result.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

-----Original Message-----

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon Lasher

Sent: Friday, March 29, 2013 9:25 PM

To: MS_Access_Professionals@yahoogroups.com

Subject: RE: [MS_AccessPros] Query not working properly

Copied using SQL view of the Query. No actual SQL code written.

SELECT Tbl_rTPDirect.CustCode, Tbl_rTPDirect.TPID, Tbl_rTPDirect.CoName,

Tbl_rTPDirect.CoAddress, Tbl_rTPDirect.CoCity, Tbl_rTPDirect.CoState,

Tbl_rTPDirect.CoZip, Tbl_rTPDirect.[Sales Name],

Tbl_rTPDirect.OtherNamesLoc, Tbl_rTPDirect.BillToName,

Tbl_rTPDirect.Comments, Tbl_rTPDirect.BillToAddress,

Tbl_rTPDirect.BillToCity, Tbl_rTPDirect.BillToState,

Tbl_rTPDirect.BillToPostal, Tbl_rTPDirect.BillToPhone,

Tbl_rTPDirect.CoPhone, Tbl_rTPDirect.CoFax, Tbl_rTPDirect.WATS,

Tbl_rTPDirect.DirectTariff, Tbl_rTPDirect.DirectDiscount,

Tbl_rTPDirect.DirectClass, Tbl_rTPDirect.DirectFloor,

Tbl_rTPDirect.Comments, Tbl_rTPDirect.[Sales Name], Tbl_rTPDirect.[Sales

Code], Tbl_rTPDirect.TPDate, Tbl_rTPDirect.OriginalDate,

Tbl_rTPDirect.Hourly, Tbl_rTPDirect.MinB1, Tbl_rTPDirect.MinB1Rate,

Tbl_rTPDirect.MinB2, Tbl_rTPDirect.MinB2Rate, Tbl_rTPDirect.PalletRate,

Tbl_rTPDirect.PalletRateLevel, Tbl_rTPDirect.DirectPRPrice,

Tbl_rTPDirect.PalletRateSpecial, Tbl_rTPDirect.PRWeight,

Tbl_rTPDirect.PupRates, Tbl_rTPDirect.PupRateSpecial,

Tbl_rTPDirect.OtherSpecial, Tbl_rTPDirect.FSLevel, Tbl_rTPDirect.CODLevel,

Tbl_rTPDirect.CODlevel1, Tbl_rTPDirect.Hazmat, Tbl_rTPDirect.Residential,

Tbl_rTPDirect.Inside, Tbl_rTPDirect.Liftgate, Tbl_rTPDirect.FAKtrailers,

Tbl_rTPDirect.Contact, Tbl_rTPDirect.Other, Tbl_rTPDirect.OtherNamesLoc,

Tbl_rTPDirect.extra, Tbl_rTPDirect.MinB2Ratea, Tbl_rTPDirect.MinB1Ratea,

Tbl_rTPDirect.DirectPRPricea, Tbl_rCalif.CATariff, Tbl_rCalif.CADiscount,

Tbl_rCalif.CAClass, Tbl_rCalif.CAFloor, Tbl_rCalif.Comments,

Tbl_rCalif.PalletWgt, Tbl_rCalif.PalletNCA, Tbl_rCalif.PalletSCA,

Tbl_rCalif.OtherCARates, Tbl_rIdaho.IdahoTariff, Tbl_rIdaho.IdahoDiscount,

Tbl_rIdaho.IdahoClass, Tbl_rIdaho.IdahoFloor, Tbl_rIdaho.PalletWgt,

Tbl_rIdaho.PalletNI, Tbl_rIdaho.PalletSI, Tbl_rIdaho.OtherIRates,

Tbl_rIdaho.IdahoComments, Tbl_rOROther.OORTariff, Tbl_rOROther.OORDiscount,

Tbl_rOROther.OORclass, Tbl_rOROther.OORFloor, Tbl_rOROther.PalletWgt,

Tbl_rOROther.PalletORPallets, Tbl_rOROther.OtherOROtherRates,

Tbl_rOROther.OtherORComments, Tbl_rOtherStates.OSTariff,

Tbl_rOtherStates.OSDiscount, Tbl_rOtherStates.OSClass,

Tbl_rOtherStates.OSFloor, Tbl_rOtherStates.Comments,

Tbl_rOtherStates.OtherOSRates, Tbl_rWash.WATariff, Tbl_rWash.WADiscount,

Tbl_rWash.WAClass, Tbl_rWash.WAFloor, Tbl_rWash.PalletWgt,

Tbl_rWash.PalletWWA, Tbl_rWash.PalletEWA, Tbl_rWash.OtherWARates,

Tbl_rWash.WashComments FROM ((((Tbl_rTPDirect INNER JOIN Tbl_rCalif ON

Tbl_rTPDirect.TPID = Tbl_rCalif.TPID) INNER JOIN Tbl_rIdaho ON

Tbl_rTPDirect.TPID = Tbl_rIdaho.TPID) INNER JOIN Tbl_rOROther ON

Tbl_rTPDirect.TPID = Tbl_rOROther.TPID) INNER JOIN Tbl_rOtherStates ON

Tbl_rTPDirect.TPID = Tbl_rOtherStates.TPID) INNER JOIN Tbl_rWash ON

Tbl_rTPDirect.TPID = Tbl_rWash.TPID;

To: MS_Access_Professionals@yahoogroups.com

From: JohnV@msn.com

Date: Fri, 29 Mar 2013 19:01:23 +0100

Subject: RE: [MS_AccessPros] Query not working properly

Lon-

I really need to see the entire SQL statement to try to spot something. But

one thing comes to mind - because you're doing INNER JOIN everywhere, you

won't get any rows if there are rows missing in related tables for the dates

you want.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

-----Original Message-----

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon Lasher

Sent: Friday, March 29, 2013 5:59 PM

To: MS_Access_Professionals@yahoogroups.com

Subject: RE: [MS_AccessPros] Query not working properly

There is a master file with customer names & certain data related to pricing

for direct service points (we are a trucking company). There are six other

tables with location specific pricing or sales representative data. Only

the five location specific tables are included in the query. The query has

inner joins between the main table and the other location specific tables.

Very simple. Worked fine until yesterday. Nothing else I know has

changed. I won't provide the list of all the table field names. This is

the part where the joins are expressed.

FROM ((((Tbl_rTPDirect INNER JOIN Tbl_rCalif ON Tbl_rTPDirect.TPID =

Tbl_rCalif.TPID) INNER JOIN Tbl_rIdaho ON Tbl_rTPDirect.TPID =

Tbl_rIdaho.TPID) INNER JOIN Tbl_rOROther ON Tbl_rTPDirect.TPID =

Tbl_rOROther.TPID) INNER JOIN Tbl_rOtherStates ON Tbl_rTPDirect.TPID =

Tbl_rOtherStates.TPID) INNER JOIN Tbl_rWash ON Tbl_rTPDirect.TPID =

Tbl_rWash.TPID;

Please remember, this still works fine for records entered prior to

yesterday. It even picks up changes in those records. It just won't yield

results from new records.

To: MS_Access_Professionals@yahoogroups.com

From: JohnV@msn.com

Date: Fri, 29 Mar 2013 17:07:45 +0100

Subject: RE: [MS_AccessPros] Query not working properly

Lon-

What is the structure of the table and the SQL of your query?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon

Sent: Friday, March 29, 2013 4:54 PM

To: MS_Access_Professionals@yahoogroups.com

Subject: [MS_AccessPros] Query not working properly

I have a 3,600 record database split into front and back ends. Users have

the front ends on their PCs. The database itself is in Access 2000 format on

a Windows 2003 server box.

I also have a Microsoft Word 2010 document with multiple fields linked to a

query in my database. To find the correct data to use in the Word document,

I used a filter based on an indexed code that is assigned to each customer

for which I want information.

Starting yesterday (March 28), any new customer entered in the database will

not show up in the Word document. If I change information in an customer's

record that existed prior to March 28, that information shows.

I finally have determined the query itself is at fault. If I run the query

itself with the new customer's ID code, the query won't find it.

I've never run into a query that quit working like this. Anyone have any

ideas? If you need more info to respond, ask away.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links