I'm trying to run a query

  • Thread starter Thread starter NMHemp
  • Start date Start date
N

NMHemp

I'm putting together a database to collect and record when employees of our
various project sites complete and return certain forms to us. What this
means is that several sites will potentially have more than one date entered.
I've created a table called "site number" and date fields for each of the
three forms. However, I would like to design a query that just shows the most
recent form we received per site. Any ideas? Nothing I've tried seems to work.
 
I'm putting together a database to collect and record when employees of our
various project sites complete and return certain forms to us. What this
means is that several sites will potentially have more than one date entered.
I've created a table called "site number" and date fields for each of the
three forms. However, I would like to design a query that just shows the most
recent form we received per site. Any ideas? Nothing I've tried seems to work.
Click to expand...

That's because of your first mistake: adding multiple date fields to the site
table. A site doesn't have a date!!!

If you have a one (site) to many (date entered) relationship, model it as a
one to many relationship - with a Sites table, SiteNumber as its primary key;
related one to many to a SiteVisits table, with a SiteNumber (nonunique)
foreign key to the Sites table, a VisitDate, and an EmployeeID.
 
I've created a table called "site number" and date fields for each of the
three forms.
It sounds like you have a spreadsheet like this ---
Site FormA FormB FormC
1 1/1/2008 2/1/2008 1/31/2008
1 1/3/2008 1/13/2008 1/3/2008
1 2/12/2008 1/1/2008 2/3/2008
2 2/2/2008 2/3/2008 2/7/2008
3 2/3/2008 2/1/2008 1/8/2008
What you need is a table like this --
Site YourDate Form
1 1/1/2008 A
1 1/3/2008 A
1 2/12/2008 A
2 2/2/2008 A
3 2/3/2008 A
1 2/1/2008 B
1 1/13/2008 B
1 1/1/2008 B
2 2/3/2008 B
3 2/1/2008 B
1 1/31/2008 C
1 1/3/2008 C
1 2/3/2008 C
2 2/7/2008 C
3 1/8/2008 C
Then use this query ---
SELECT [YourTable].Site, [YourTable].Form, Max([YourTable].[YourDate]) AS
[MaxOfYourDate]
FROM YourTable
GROUP BY [YourTable].Site, [YourTable].Form;
 
Thanks.

Worked perfectly!

KARL DEWEY said:
three forms.
It sounds like you have a spreadsheet like this ---
Site FormA FormB FormC
1 1/1/2008 2/1/2008 1/31/2008
1 1/3/2008 1/13/2008 1/3/2008
1 2/12/2008 1/1/2008 2/3/2008
2 2/2/2008 2/3/2008 2/7/2008
3 2/3/2008 2/1/2008 1/8/2008
What you need is a table like this --
Site YourDate Form
1 1/1/2008 A
1 1/3/2008 A
1 2/12/2008 A
2 2/2/2008 A
3 2/3/2008 A
1 2/1/2008 B
1 1/13/2008 B
1 1/1/2008 B
2 2/3/2008 B
3 2/1/2008 B
1 1/31/2008 C
1 1/3/2008 C
1 2/3/2008 C
2 2/7/2008 C
3 1/8/2008 C
Then use this query ---
SELECT [YourTable].Site, [YourTable].Form, Max([YourTable].[YourDate]) AS
[MaxOfYourDate]
FROM YourTable
GROUP BY [YourTable].Site, [YourTable].Form;

--
KARL DEWEY
Build a little - Test a little


NMHemp said:
I'm putting together a database to collect and record when employees of our
various project sites complete and return certain forms to us. What this
means is that several sites will potentially have more than one date entered.
I've created a table called "site number" and date fields for each of the
three forms. However, I would like to design a query that just shows the most
recent form we received per site. Any ideas? Nothing I've tried seems to work.
Click to expand...
Click to expand...
 
Back
Top