find students that showed up for 2 specific dates

[jairgarza]

Hi there,



I am in a bit of a duh moment.... 1 table with students ID's and names along with a date... kind of a roster... I want to be able to query the table and get ONLY students that showed up for 2 specific dates (not range, but 2 individual dates)... I have been looking way to long to the dup expression builder statement and I cannot figur out how o include to only get the dups with a specific date (2).

Any ideas or suggestions will be greatly appreciated

Table1
ID
Date in

parameters: [first date] [second date]

find in Table 1 all ID's that were present in both dates: 1-7 and 1-12

thanks

[orange]

What exactly does this represent 1-7 and 1-12?

For reference,
use meaningful table names
don't use a naming convention that uses embedded spaces in names
show readers what you have tried

Good luck with your project.

[jairgarza]

>>What exactly does this represent 1-7 and 1-12?

both dates: 1-7 and 1-12

they represent dates: January 7 and January 12

[orange]

Any particular Year?

[jairgarza]

table only has this year, so no... I guess if year included could help for future year queries (if I continue to use it longer than this year)... thanks for asking

[John_G]

You can get the list of student ID's with a simple query, which you can then use to filter other queries by Student_ID. You need two because the first one has a group by in it, which limits the number of fields you can put in.

For example: I have a stamp collection, and I want to know which countries have both stamp number 1800 AND 1900 in the collection. In query design view, I do this:



The SQL for the query is this (with the table name usually removed to make it easier to read:

SELECT COUNTRYNUM, Count(COUNTRYNUM) AS CountOfCOUNTRYNUM
FROM [Main Collection]
WHERE (FIRST=1800 Or FIRST=1900)
GROUP BY COUNTRYNUM
HAVING Count(COUNTRYNUM)>1;

Notice COUNTRYNUM is in the query twice - first to group by and display, and second to count how many times it occurs.
Notice too that the two conditions are joined with OR; if both of them are met, then the count will be > 1.

You would replace COUNTRYNUM with Student_ID, FIRST with the name of your date field, and Main Collection with the name of your table. (I know - shouldn't use spaces in table names)

Once you have this working, you would join it to other queries on Student_ID to filter the data they show.

[orange]

Redid the query with my sample data

PARAMETERS  [Date1]  datetime, [date2]  datetime;
SELECT AnimalID 
FROM AnimalLocs as A
WHERE A.SightingDate = [date1]
AND
 a.animalID  in (SELECT  AnimalID 
FROM AnimalLocs as B
WHERE  b.SightingDate =[date2] )

Hope it's useful.

[jairgarza]

Hi John_G,

Thanks for the idea. I will try it as soon as I get my Network back (split DB).... One difference is that I want user to enter the dates (criteria)... in your example would having the popup to input 1800 and 1900 and the run the query, this will need to be done I assume with parameters, I think this is what I was thinking but I have not been able to get it to work... let me try and I will post the outcome.... thanks again!

You can get the list of student ID's with a simple query, which you can then use to filter other queries by Student_ID. You need two because the first one has a group by in it, which limits the number of fields you can put in.

For example: I have a stamp collection, and I want to know which countries have both stamp number 1800 AND 1900 in the collection. In query design view, I do this:



The SQL for the query is this (with the table name usually removed to make it easier to read:

SELECT COUNTRYNUM, Count(COUNTRYNUM) AS CountOfCOUNTRYNUM
FROM [Main Collection]
WHERE (FIRST=1800 Or FIRST=1900)
GROUP BY COUNTRYNUM
HAVING Count(COUNTRYNUM)>1;

Notice COUNTRYNUM is in the query twice - first to group by and display, and second to count how many times it occurs.
Notice too that the two conditions are joined with OR; if both of them are met, then the count will be > 1.

You would replace COUNTRYNUM with Student_ID, FIRST with the name of your date field, and Main Collection with the name of your table. (I know - shouldn't use spaces in table names)

Once you have this working, you would join it to other queries on Student_ID to filter the data they show.

[June7]

Possibly another approach:

SELECT * FROM table WHERE [datefield]=#1/7/2007# AND StudID IN (SELECT StudID FROM table WHERE [datefield]=#1/12/2007#);


If you want dynamic parameterized query, recommend not utilizing popup input prompt. Have user enter values on a form and the query references the controls on form. This way you can validate user input.


Also advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.

[jairgarza]

John_G,

I am sorry for the delayed reply: it worked perfect, thanks!!!!