Greg Gibson said:
I have one flat table, imported from a .csv file dumped from a main frame.
Name Date
Gibson 1/1/1900
Hoover 1/1/1900
Carver 1/1/1900
Smith 1/1/1900
Gibson 2/1/1900
Hoover 3/1/1900
Hoover 4/1/1900
Smith 5/1/1900
Gibson 6/1/1900
Hoover 7/1/1900
What'd I like is a result like this:
Name DateA DateB DateC Number_of_dates
Carver 1/1/1900 1
Gibson 6/1/1900 2/1/1900 1/1/1900 3
Hoover 7/1/1900 4/1/1900 3/1/1900 4 (or 3)
Smith 5/1/1900 1/1/1900 2
So, in summary, pull out the last three dates, drop any others, and tell me
how many were found.
Where there are more than three dates, number-of-dates can be either 3, or
the number of
actual entries, it makes no difference to my application.
I have this working, using a temporary table, a create table query with some
vba,
and a bunch of queries that pull each date out from that temp table, but I
am looking
for an elegant sql solution, just because I feel like a better solution must
exist.
Hi Greg,
Here be a 2-query method that may work for you.
I called your dump table "tblDump"
and renamed your fields to "AName" and "ADate"
so did not use reserved words.
- query 1 ("qryDump0")
SELECT t1.AName,
t1.ADate AS t1Date,
t2.ADate AS t2Date,
t3.ADate AS t3Date
FROM (tblDump AS t1
LEFT JOIN tblDump AS t2
ON (t1.ADate>t2.ADate)
AND (t1.AName = t2.AName))
LEFT JOIN tblDump AS t3
ON (t2.ADate>t3.ADate)
AND (t2.AName = t3.AName)
ORDER BY t1.AName;
producing (from your example data):
AName t1Date t2Date t3Date
Carver 1/1/1900
Gibson 6/1/1900 1/1/1900
Gibson 6/1/1900 2/1/1900 1/1/1900
Gibson 2/1/1900 1/1/1900
Gibson 1/1/1900
Hoover 7/1/1900 1/1/1900
Hoover 7/1/1900 3/1/1900 1/1/1900
Hoover 7/1/1900 4/1/1900 3/1/1900
Hoover 7/1/1900 4/1/1900 1/1/1900
Hoover 4/1/1900 1/1/1900
Hoover 4/1/1900 3/1/1900 1/1/1900
Hoover 3/1/1900 1/1/1900
Hoover 1/1/1900
Smith 5/1/1900 1/1/1900
Smith 1/1/1900
You won't be able to complete this
query in QBE because of the joins,
but you can start there in Design View
then switch to SQL View to finish.
(pardon me if you already know the following)
Add your table 3 times to query design.
Right-mouse click on the first table instance,
choose Properties, and set Alias to "t1"
Likewise, set Alias for second instance
to "t2", and Alias for third instance to "t3"
Drag-and-drop t1.AName over on t2.AName
to create a join. Right-mouse click on this join
line and choose "Join Properties." Select option 2
("Include ALL records from 't1' and only those
records from 't2' where the joined fields are equal.")
Drag-and-drop t2.AName over on t3.AName
to create a join. Right-mouse click on this join
line and choose "Join Properties." Select option 2
("Include ALL records from 't2' and only those
records from 't3' where the joined fields are equal.")
Drag t1.AName down to a field row in grid.
Drag t1.ADate down to a field row in grid in next column.
Give it an alias by editing field row to
t1Date: ADate
Drag t2.ADate down to a field row in grid in next column.
Give it an alias by editing field row to
t2Date: ADate
Drag t3.ADate down to a field row in grid in next column.
Give it an alias by editing field row to
t3Date: ADate
Your query would now look like the following
in SQL View:
SELECT t1.AName,
t1.ADate AS t1Date,
t2.ADate AS t2Date,
t3.ADate AS t3Date
FROM (tblDump AS t1
LEFT JOIN tblDump AS t2
ON t1.AName = t2.AName)
LEFT JOIN tblDump AS t3
ON t2.AName = t3.AName;
From here on you will have to
edit the joins in SQL view.
We'd like to return only those t2 dates that are
less than t1 dates where ANames match
and only those t3 dates that are
less than t2 dates where ANames match.
SELECT t1.AName,
t1.ADate AS t1Date,
t2.ADate AS t2Date,
t3.ADate AS t3Date
FROM (tblDump AS t1
LEFT JOIN tblDump AS t2
ON (t1.AName = t2.AName)
AND (t1.ADate>t2.ADate))
LEFT JOIN tblDump AS t3
ON (t2.AName = t3.AName)
AND (t2.ADate>t3.ADate)
ORDER BY t1.AName;
Once we have these results,
all we have to do is group the results
by AName, then find the Max date
for each date column in each group,
and finally, provide a correlated
subquery to count dates for each group.
- query 2 ("qryDumpSummary")
SELECT t4.AName,
Max(t4.t1Date) AS DateA,
Max(t4.t2Date) AS DateB,
Max(t4.t3Date) AS DateC,
(SELECT Count(t5.ADate)
FROM tblDump AS t5
WHERE t5.AName=t4.AName) AS Number_of_dates
FROM qryDump0 AS t4
GROUP BY t4.AName
ORDER BY t4.AName;
producing
AName DateA DateB DateC Number_of_dates
Carver 1/1/1900 1
Gibson 6/1/1900 2/1/1900 1/1/1900 3
Hoover 7/1/1900 4/1/1900 3/1/1900 4
Smith 5/1/1900 1/1/1900 2
Please respond back if I have misunderstood
or something is not clear.
Good luck,
Gary Walter
