Returning all records from specified date

[Guest]

I'm sure this is an easy query but I am not familiar with Access.
I am doing a query from a single table and would like to first find all the
dates when a particular SampleID matches the input criteria. Then I would
like to pull all records for any date on which that SampleID occurs.

Example of database:

SampleID RecordDate
2099 1-1-06
2100 1-1-06
2100 1-2-06
2101 1-2-06
2099 1-3-06
2200 1-3-06
2200 1-4-06

So if the user searches for SampleID 2099 the query will return:

SampleID RecordDate
2099 1-1-06
2100 1-1-06
2099 1-3-06
2200 1-3-06

Thanks for any help.

 

[Roger Carlson]

You can do this with a subquery, like so:

SELECT Table14.SampleID, Table14.RecordDate
FROM Table14
WHERE Table14.RecordDate In (SELECT Table14.RecordDate
FROM Table14
WHERE Table14.SampleID=[Enter Sample Number]);

Just replace Table14 with the actual name of your table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Use two queries. The first returns all of the dates that the SampleID
occured. Then using the dates of that query as criteria pull all records
with those dates.

 

[John Welch]

This should work for you. It uses a self join (joining a table to an alias
of itself), which is a useful thing you might want to read about in help or
google or in a book. Here, I am using "foo" as an alias for your table. I
also made a parameter in the query ( "[Enter Sample ID Number]"), so that it
will ask you for the sample ID everytime you run the query. You can take
that out and put in a number if you want.

SELECT YourTableName.SampleID, YourTableName.RecordDate
FROM YourTableName INNER JOIN YourTableName as FOO ON
YourTableName.RecordDate= FOO.RecordDate
WHERE (((FOO.SampleID)=[Enter Sample ID Number]));

Hope this helps
-John

 

[Guest]

Thanks Roger. That did just what I was after in a simple query.

Karl, running two queries was also my first approach but I wanted a one step
approach since this will be running from an Excel macro to pull the data into
a spread sheet for further manipulation.

John, thanks I'll read up on using aliases. I've used something similar in
a SQL server query but didn't know how to get Access to do the same thing.