Returning all records from specified date

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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.
 
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
 
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.
 
Back
Top