Select 'Last' query missing records - SQL code included

[DataGuy]

Hello!

My query is designed to return a list of the most recently received CAD files for all part numbers in a certain product line.

In SQL, it looks like this:

SELECT Last([CAD LOG].[Transfer Date]) AS [LastOfTransfer Date1], [CAD LOG].[PART #], [CAD LOG].CompanyName, Last([CAD LOG].[LTD Filename]) AS [LastOfLTD Filename], Last([CAD LOG].[Revision Date (if known)]) AS [LastOfRevision Date (if known)], Last([CAD LOG].[Revision Level or Revision #]) AS [LastOfRevision Level or Revision #], Last([CAD LOG].[Data Used for Tooling?]) AS [LastOfData Used for Tooling?], Last([CAD LOG].[Picture Link 1]) AS [LastOfPicture Link 1]
FROM [CAD LOG]
WHERE ((([CAD LOG].[Program #])=[Enter Program #, as 'PGM#######']) AND (([CAD LOG].[To or From])="From"))
GROUP BY [CAD LOG].[PART #], [CAD LOG].CompanyName
HAVING ((([CAD LOG].CompanyName)=[Enter Company Name data is from]))
ORDER BY Last([CAD LOG].[Transfer Date]) DESC;

It seemed to work perfectly, until I noticed that it missed a record. I deleted the record it missed, and re-entered it. Then I re-ran my query. It still would not show up. I went through each feild and checked spelling, case, everything about the record. Still, I cannot understand why it would not be included in the results.

I tried removing feilds from the query 1 at a time to see if one of them was throwing off the results - no luck.

I created the query in 'Design View' which I somewhat understand. I included the SQL code (which I don't really understand) because it seems that people are able to help better using that on this newsgroup.

Thank you,

Dataguy

 

[Guest]

Two suggestions to check:

1) Is [Program #] set as a number or text? If it's as a number and there are
leading zeros, this might create a problem.
2) You have two user inputs - apart from letter case, make sure that all
info being entered is EXACTLY as it appears in your data.

Otherwise, if you could provide specifics on the record that is being
missed, that would be helpful.

 

[DataGuy]

Data is always entered using InfoPath 2003, so it's not possible to have
errors in the feilds actually. I'm using Access for it's reporting
abilities.

Program# is set as text.

What would be helpful to you about the missed record?

Two suggestions to check:

1) Is [Program #] set as a number or text? If it's as a number and there
are
leading zeros, this might create a problem.
2) You have two user inputs - apart from letter case, make sure that all
info being entered is EXACTLY as it appears in your data.

Otherwise, if you could provide specifics on the record that is being
missed, that would be helpful.

Hello!

My query is designed to return a list of the most recently received CAD
files for all part numbers in a certain product line.

In SQL, it looks like this:

SELECT Last([CAD LOG].[Transfer Date]) AS [LastOfTransfer Date1], [CAD
LOG].[PART #], [CAD LOG].CompanyName, Last([CAD LOG].[LTD Filename]) AS
[LastOfLTD Filename], Last([CAD LOG].[Revision Date (if known)]) AS
[LastOfRevision Date (if known)], Last([CAD LOG].[Revision Level or
Revision #]) AS [LastOfRevision Level or Revision #], Last([CAD
LOG].[Data Used for Tooling?]) AS [LastOfData Used for Tooling?],
Last([CAD LOG].[Picture Link 1]) AS [LastOfPicture Link 1]
FROM [CAD LOG]
WHERE ((([CAD LOG].[Program #])=[Enter Program #, as 'PGM#######']) AND
(([CAD LOG].[To or From])="From"))
GROUP BY [CAD LOG].[PART #], [CAD LOG].CompanyName
HAVING ((([CAD LOG].CompanyName)=[Enter Company Name data is from]))
ORDER BY Last([CAD LOG].[Transfer Date]) DESC;

It seemed to work perfectly, until I noticed that it missed a record. I
deleted the record it missed, and re-entered it. Then I re-ran my query.
It still would not show up. I went through each feild and checked
spelling, case, everything about the record. Still, I cannot understand
why it would not be included in the results.

I tried removing feilds from the query 1 at a time to see if one of them
was throwing off the results - no luck.

I created the query in 'Design View' which I somewhat understand. I
included the SQL code (which I don't really understand) because it seems
that people are able to help better using that on this newsgroup.

Thank you,

Dataguy

 

[Chris2]

Data is always entered using InfoPath 2003, so it's not possible to have
errors in the feilds actually. I'm using Access for it's reporting
abilities.

Program# is set as text.

What would be helpful to you about the missed record?

DataGuy,

If we don't know what the data in the missing row is, there is no way
to figure out why the row isn't appearing in the output recordset.


Sincerely,

Chris O.