By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,407 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

My query doesn't seem to be pulling all possible records.

P: 56
I have a query that is pulling from a table of 35000+ records But for some reason any records beyond 25999 are not coming thru.

The Table is a list employees - and thier assignments - so there are many records with the same employee - thus the 35000+ records.

When I pull the query - I get all employees numbered 25841 and below...but the ones that are numbered 26000+ won't show. I can filter for those individually on the table itself, but even if I try to query just for those employee's in the 26000 range - they won't show up?????

I am confused! Is there a reason this is happening?

I guess it would be helpful to know that when pulling from the sql server (where the original table resides) - it seems to work okay - I made a copy of the table put it on a disc and copied it to my lap top so I could "fix" the database at home - cause there aren't enough hours in the day....
Nov 16 '06 #1
Share this Question
Share on Google+
20 Replies


Expert 5K+
P: 8,434
A couple of thoughts:
  • Could you post the SQL from the "bad" query?
  • Is it possible that you failed to copy all the records?
Nov 16 '06 #2

P: 56
A couple of thoughts:
  • Could you post the SQL from the "bad" query?
  • Is it possible that you failed to copy all the records?
I thought I might not have copied all the records...but the standalone table has the records above 25841 in it

The query - is a simple query...

here is the code... (sorry there are lots of fields)

SELECT qryAllDemosbyDateRange.ProjectCode, qryAllDemosbyDateRange.[Employee Number], qryAllDemosbyDateRange.[Demo Date], qryAllDemosbyDateRange.Store_No, qryAllDemosbyDateRange.[First Name], qryAllDemosbyDateRange.[Last Name], qryAllDemosbyDateRange.Shirt_size, qryAllDemosbyDateRange.ShippingNumber, qryPanasonicShirtCheckNew2.[Employee Number]
FROM qryAllDemosbyDateRange RIGHT JOIN (qryPanasonicShipCheckNewProgram LEFT JOIN qryPanasonicShirtCheckNew2 ON qryPanasonicShipCheckNewProgram.qryAllDemosByDateR ange.[Employee Number] = qryPanasonicShirtCheckNew2.[Employee Number]) ON qryAllDemosbyDateRange.[Employee Number] = qryPanasonicShipCheckNewProgram.qryAllDemosByDateR ange.[Employee Number]
WHERE (((qryPanasonicShirtCheckNew2.[Employee Number]) Is Null));

could it be the left and right joins?

There are only 4 fields that I am using criteria on....
Nov 16 '06 #3

Expert 5K+
P: 8,434
I thought I might not have copied all the records...but the standalone table has the records above 25841 in it
Drat! :)

I'll leave it to the SQL gurus to go into the query in depth. But in the meantime, could you maybe try out the individual tests that are being performed in there? For example, one says qryPanasonicShirtCheckNew2.[Employee Number]) Is Null - is it possible you have accidentally removed some null values? Maybe updated them to zero? (It's not the same thing.)

Just speaking for myself, I definitely would suspect the joins.
Nov 16 '06 #4

P: 56
Well, It's not the joins.

the is null...is supposed to find unmatched records...which is what i am trying to do....but it's not working.

Any other ideas?
Nov 16 '06 #5

P: 56
Well...I guess it would help to have the right query pasted in here...



SELECT vewDemoData.ProjectCode, vewDemoData.[Demo Date], vewDemoData.[Start Dt], vewDemoData.[End Dt], vewDemoData.[Date Assigned], vewDemoData.[Store #] AS Store_No, vewStores.[Retailer Abbrev] AS Account_ID, vewStores.[Address 1] AS StoreAdd, vewStores.City AS StoreCity, vewStores.State AS StoreState, vewStores.Zip AS StoreZip, vewStores.Phone AS StorePhone, vewRepresentative.PersonID AS [Employee Number], vewRepresentative.PersonFName AS [First Name], vewRepresentative.PersonLName AS [Last Name], vewRepresentative.PrimaryAddress1 AS [Rep Address], vewRepresentative.PrimaryCity AS [Rep City], vewRepresentative.PrimaryState AS [Rep State], vewRepresentative.PrimaryZip AS [Rep Zip], vewRepresentative.PhoneNumber AS Telephone, vewRepresentative.Email AS [e-mail address], vewStores.LocationNameID AS Store_ID, vewDemoData.[Start Time] AS DEMO_START, vewDemoData.[End Time] AS DEMO_END, vewDemoData.[Project Name] AS Vendor, vewRepresentative.ShirtSizeDescription AS Shirt_size, vewDemoData.[Rep ID], vewShippingNumber.ShippingNumber, *
FROM (((vewDemoData LEFT JOIN vewStores ON vewDemoData.[Store #]=vewStores.[Store #]) LEFT JOIN vewRepresentative ON vewDemoData.[Rep ID]=vewRepresentative.PersonID) LEFT JOIN vewEmployeeShirtSize ON vewRepresentative.PersonID=vewEmployeeShirtSize.Pe rsonID) LEFT JOIN vewShippingNumber ON vewRepresentative.PrimaryState=vewShippingNumber.S hippingNumberState
WHERE (((vewDemoData.ProjectCode)="d00646") AND ((vewDemoData.[Demo Date])=#11/18/2006# Or (vewDemoData.[Demo Date])=#11/19/2006#) AND ((vewDemoData.[Date Assigned])<=#11/15/2006#) AND ((vewRepresentative.PersonID) Is Not Null));
Nov 16 '06 #6

Expert 5K+
P: 8,434
Well...I guess it would help to have the right query pasted in here...
Yeah, sometimes helps a bit. :)

I'm just leaving work, but I'm sure NeoPa or mmccarthy will be able to help.
Nov 16 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT
vewDemoData.ProjectCode,
vewDemoData.[Demo Date],
vewDemoData.[Start Dt],
vewDemoData.[End Dt],
vewDemoData.[Date Assigned],
vewDemoData.[Store #] AS Store_No,
vewStores.[Retailer Abbrev] AS Account_ID,
vewStores.[Address 1] AS StoreAdd,
vewStores.City AS StoreCity,
vewStores.State AS StoreState,
vewStores.Zip AS StoreZip,
vewStores.Phone AS StorePhone,
vewRepresentative.PersonID AS [Employee Number], vewRepresentative.PersonFName AS [First Name], vewRepresentative.PersonLName AS [Last Name], vewRepresentative.PrimaryAddress1 AS [Rep Address], vewRepresentative.PrimaryCity AS [Rep City], vewRepresentative.PrimaryState AS [Rep State], vewRepresentative.PrimaryZip AS [Rep Zip], vewRepresentative.PhoneNumber AS Telephone, vewRepresentative.Email AS [e-mail address], vewStores.LocationNameID AS Store_ID,
vewDemoData.[Start Time] AS DEMO_START,
vewDemoData.[End Time] AS DEMO_END,
vewDemoData.[Project Name] AS Vendor, vewRepresentative.ShirtSizeDescription AS Shirt_size,
vewDemoData.[Rep ID],
vewShippingNumber.ShippingNumber, * (<--- What is this?)
FROM (((vewDemoData LEFT JOIN vewStores
ON vewDemoData.[Store #]=vewStores.[Store #])
LEFT JOIN vewRepresentative
ON vewDemoData.[Rep ID]=vewRepresentative.PersonID)
----------------------------------------------
You never use this that I can see just leave it out.
LEFT JOIN vewEmployeeShirtSize
ON vewRepresentative.PersonID=vewEmployeeShirtSize.Pe rsonID)
--------------------------------------------------
LEFT JOIN vewShippingNumber
ON vewRepresentative.PrimaryState=vewShippingNumber.S hippingNumberState
WHERE (((vewDemoData.ProjectCode)="d00646")
AND ((vewDemoData.[Demo Date])=#11/18/2006# Or (vewDemoData.[Demo Date])=#11/19/2006#)
AND ((vewDemoData.[Date Assigned])<=#11/15/2006#)
AND ((vewRepresentative.PersonID) Is Not Null));

Otherwise the first left join vewDemoData will determine the size of your data return. However, '(vewRepresentative.PersonID) Is Not Null' will remove all records that don't have a corresponding record in vewRepresentative negating the left join.
Nov 16 '06 #8

NeoPa
Expert Mod 15k+
P: 31,494
, *
is found in queries where the developer put in 'SELECT *' as an early version (of the SQL) to test the link structure.
I don't believe it shows in design view so is often overlooked.
Nov 16 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
is found in queries where the developer put in 'SELECT *' as an early version (of the SQL) to test the link structure.
I don't believe it shows in design view so is often overlooked.
Makes sense.

I think in this instance with so many links that it could affect the result.

Mary
Nov 16 '06 #10

PEB
Expert 100+
P: 1,418
PEB
So in your query all records from vewDemoData will be displayed where the records respond to the criteria for date of assignement!

Can you remove the condition for assignement dates and say if all records will appear or not!
Nov 18 '06 #11

P: 56
So in your query all records from vewDemoData will be displayed where the records respond to the criteria for date of assignement!

Can you remove the condition for assignement dates and say if all records will appear or not!

I really can't. The assignment date is used to determine new assignments versus old ones...

the is not null that is in the query is pretty much a place holder, as none of the employee numbers are null. We use the employee number field in another step to isolate the people who come up in another query, so we can compare them to yet another table.

I think we have too much going on in this database...and it is affecting it's performance.

is there a limit to the number of records an access query can go thru? We have many small tables that we use in a very manual process...and it works...but it takes forever...I thought if we combined all the tables into one...that we would be able to eliminate a few steps...thus speeding up the process.
Nov 19 '06 #12

PEB
Expert 100+
P: 1,418
PEB
Really I don't think that 20000 or 30000 records are the limits of the Access tables...

I've tables with more than 1000 000 records and no such problems.. And they are used everyday a lot... So can't agree that it's coz using...

There is something that doesn't work and if you aren't able to test and isolate tables to see where is the problem it will resist unsolved!
Nov 19 '06 #13

Expert 5K+
P: 8,434
I really can't. The assignment date is used to determine new assignments versus old ones...
I think what PEB meant was just to remove the assignment date condition from this query temporarily as a step in debugging, to test its precise effect.

(Of course, it's possible I misinterpreted his suggestion.)
Nov 19 '06 #14

PEB
Expert 100+
P: 1,418
PEB
Just this is mind Killer
10x!

I think what PEB meant was just to remove the assignment date condition from this query temporarily as a step in debugging, to test its precise effect.

(Of course, it's possible I misinterpreted his suggestion.)
Nov 19 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
In fact to go further

I would create a test query with the where statement completely removed and check that the records being returned are what you expect. If not then we will know that the problem lies in the join expressions and we can concentrate on the table structures.

Joining your tables into one table will not solve your problems. Only create new ones. If you are having problems querying the tables then the problem more than likely lies in the relationships between those tables.

Run the test described above and if there is a problem, list out the relevant tables and give the relationships between each of the tables. Describe any fields you are joining tables on and the field datatypes. Also the one to one and one to many relationship between the tables.

From there we should be able to work out the problem.

Mary
Nov 19 '06 #16

P: 56
In fact to go further

I would create a test query with the where statement completely removed and check that the records being returned are what you expect. If not then we will know that the problem lies in the join expressions and we can concentrate on the table structures.

Joining your tables into one table will not solve your problems. Only create new ones. If you are having problems querying the tables then the problem more than likely lies in the relationships between those tables.

Run the test described above and if there is a problem, list out the relevant tables and give the relationships between each of the tables. Describe any fields you are joining tables on and the field datatypes. Also the one to one and one to many relationship between the tables.

From there we should be able to work out the problem.

Mary

I think what I am trying to do is ultimately not 100% possible in Access.

I am going to start from scratch and see if I can simplify it that way.

What I am trying to do is determine, from a list of new assignments (vewdemodata), who was recently assigned to a new project so that I can ship them thier "instruction packet".
(this is done by the qryDemoDataByDateRange)
Here's the sql for the query - I don't know how to make it look neat...sorry
Expand|Select|Wrap|Line Numbers
  1. SELECT vewDemoData.ProjectCode, vewDemoData.[Demo Date], vewDemoData.[Start Dt], vewDemoData.[End Dt], vewDemoData.[Date Assigned], vewDemoData.[Store #] AS Store_No, vewStores.[Retailer Abbrev] AS Account_ID, vewStores.[Address 1] AS StoreAdd, vewStores.City AS StoreCity, vewStores.State AS StoreState, vewStores.Zip AS StoreZip, vewStores.Phone AS StorePhone, vewRepresentative.PersonID AS [Employee Number], vewRepresentative.PersonFName AS [First Name], vewRepresentative.PersonLName AS [Last Name], vewRepresentative.PrimaryAddress1 AS [Rep Address], vewRepresentative.PrimaryCity AS [Rep City], vewRepresentative.PrimaryState AS [Rep State], vewRepresentative.PrimaryZip AS [Rep Zip], vewRepresentative.PhoneNumber AS Telephone, vewRepresentative.Email AS [e-mail address], vewStores.LocationNameID AS Store_ID, vewDemoData.[Start Time] AS DEMO_START, vewDemoData.[End Time] AS DEMO_END, vewDemoData.[Project Name] AS Vendor, vewRepresentative.ShirtSizeDescription AS Shirt_size, vewDemoData.[Rep ID], vewShippingNumber.ShippingNumber
  2. FROM (((vewDemoData LEFT JOIN vewStores ON vewDemoData.[Store #] = vewStores.[Store #]) LEFT JOIN vewRepresentative ON vewDemoData.[Rep ID] = vewRepresentative.PersonID) LEFT JOIN vewEmployeeShirtSize ON vewRepresentative.PersonID = vewEmployeeShirtSize.PersonID) LEFT JOIN vewShippingNumber ON vewRepresentative.PrimaryState = vewShippingNumber.ShippingNumberState
  3. WHERE (((vewDemoData.ProjectCode) In ("d00646")) AND ((vewDemoData.[Demo Date]) Between #11/17/2006# And #11/20/2006#) AND ((vewDemoData.[Date Assigned])<#11/16/2006#) AND ((vewRepresentative.PersonID) Is Not Null));
  4.  
I need to isolate those people and then find out if they have ever been shipped a shirt which is in another table - (tblshirtshipcheck). Which I have qryShirtShipCheckNew doing that so far.

Here it the code for that query
Expand|Select|Wrap|Line Numbers
  1. SELECT qryAllDemosByDateRange.[Employee Number], qryAllDemosByDateRange.[First Name], qryAllDemosByDateRange.[Last Name], tblPanasonicShirtShipCheckNew.[Employee Number]
  2. FROM qryAllDemosByDateRange LEFT JOIN tblPanasonicShirtShipCheckNew ON qryAllDemosByDateRange.[Employee Number] = tblPanasonicShirtShipCheckNew.[Employee Number]
  3. GROUP BY qryAllDemosByDateRange.[Employee Number], qryAllDemosByDateRange.[First Name], qryAllDemosByDateRange.[Last Name], tblPanasonicShirtShipCheckNew.[Employee Number]
  4. HAVING (((qryAllDemosByDateRange.[Employee Number]) Is Not Null) AND ((tblPanasonicShirtShipCheckNew.[Employee Number]) Is Null))
  5. ORDER BY qryAllDemosByDateRange.[Employee Number];
  6.  

OK...This is where I get stuck...
I need to know of the original list of people who needs the new instruction packet but doesn't need a shirt.

Current process to find this is to paste the list of people into xcel and concatinate thier id's with or and put them into the critera for Employee Number in the qryDemoDataByDateRange and re-run the query

I want access to determine both who needs instructions and a shirt, and who just needs a shirt, so I can print out a report for each and labels for each.
Nov 19 '06 #17

NeoPa
Expert Mod 15k+
P: 31,494
Is it true that you have a query that returns the PK IDs of those people who need the 'new instruction packet' and a separate query that returns the PK IDs of those people who need a shirt?

If so, then we'll call the first query's SQL SQL1 and the second query's SQL SQL2. This should not be confused with subSQL1 and subSQL2 which are created within the code and would not be replaced by your real code.
Both queries can be assumed to return a field called [Employee Number].
Expand|Select|Wrap|Line Numbers
  1. SELECT subSQL1.[Employee Number] {and any other fields required}
  2. FROM (SQL1) AS subSQL1 LEFT JOIN (SQL2) AS subSQL2 ON subSQL1.[Employee Number]=subSQL2.[Employee Number]
  3. WHERE (subSQL2.[Employee Number] IS Null)
Nov 19 '06 #18

P: 56
Is it true that you have a query that returns the PK IDs of those people who need the 'new instruction packet' and a separate query that returns the PK IDs of those people who need a shirt?

If so, then we'll call the first query's SQL SQL1 and the second query's SQL SQL2. This should not be confused with subSQL1 and subSQL2 which are created within the code and would not be replaced by your real code.
Both queries can be assumed to return a field called [Employee Number].
Expand|Select|Wrap|Line Numbers
  1. SELECT subSQL1.[Employee Number] {and any other fields required}
  2. FROM (SQL1) AS subSQL1 LEFT JOIN (SQL2) AS subSQL2 ON subSQL1.[Employee Number]=subSQL2.[Employee Number]
  3. WHERE (subSQL2.[Employee Number] IS Null)

Ok, I think I have tried this before...but I am not sure...

does this look right, I am not sure about the subSQL reference.

Expand|Select|Wrap|Line Numbers
  1. SELECT qryPanasonicShipCheckNewProgram.qryAllDemosByDateRange.[Employee Number], qryPanasonicShipCheckNewProgram.[Demo Date], qryPanasonicShipCheckNewProgram.[First Name], qryPanasonicShipCheckNewProgram.[Last Name], qryPanasonicShirtCheckNew.tblAllPanasonicShipCheckNew.[Employee Number], *
  2. FROM qryPanasonicShipCheckNewProgram LEFT JOIN qryPanasonicShirtCheckNew ON qryPanasonicShipCheckNewProgram.qryAllDemosByDateRange.[Employee Number] = qryPanasonicShirtCheckNew.qryAllDemosByDateRange.[Employee Number]
  3. WHERE (((qryPanasonicShirtCheckNew.tblAllPanasonicShipCheckNew.[Employee Number]) Is Null));
  4.  
Nov 20 '06 #19

P: 56
Is it true that you have a query that returns the PK IDs of those people who need the 'new instruction packet' and a separate query that returns the PK IDs of those people who need a shirt?

If so, then we'll call the first query's SQL SQL1 and the second query's SQL SQL2. This should not be confused with subSQL1 and subSQL2 which are created within the code and would not be replaced by your real code.
Both queries can be assumed to return a field called [Employee Number].
Expand|Select|Wrap|Line Numbers
  1. SELECT subSQL1.[Employee Number] {and any other fields required}
  2. FROM (SQL1) AS subSQL1 LEFT JOIN (SQL2) AS subSQL2 ON subSQL1.[Employee Number]=subSQL2.[Employee Number]
  3. WHERE (subSQL2.[Employee Number] IS Null)

I think it's working! I made a secondary query that showed the records in the 1st query - I think that is what you meant by the sub query...which allowed me to compare the data for the two querys. and show independant lists for each.

Then I created maketables for both of the orignial queries...and did unmatched queries of the tables...and got the same info...

Thank you for all your help! I will have to check this to my db at work...of course to make sure it really is working..and let you know!
Nov 20 '06 #20

Expert 5K+
P: 8,434
I think it's working! I made a secondary query ...
Then I created maketables for both of the orignial queries...and did unmatched queries of the tables...and got the same info...
Thank you for all your help! I will have to check this to my db at work...
It sounds as though you have a well-organised development and testing regime going there - good for you!
Nov 20 '06 #21

Post your reply

Sign in to post your reply or Sign up for a free account.