Date ranking

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

Guest

I have a table that has fields SSN, DATE1, DATE2, DATE3, DATE4. Every record
has at least one of the dates populated. However, the dates are scattered at
random somewhat and don't neatly follow the DATE1, 2, 3, 4 order. I need to
write a query that returns SSN and the top 3 dates, ranked in order. Can
anyone lend a hand?

Thanks
 
Ranked in order? What do you mean?


With the limited information you gave us, I would guess your structure is
flawed. Sounds like you should have a one-to-many design here where you
have a tbel with only two fields (SSN and FldDate). If a particular person
needs to enter one date, they would have one record in the table. If they
need three dates, they would have three records in the table, etc.

If the dates actually mean something (Date1 is initial visit), date 2 is
birthdate, etc. I would gues that you would have given them more meaningful
names. Without more details, I could be way off.

Rick B
 
I'm importing data into a new system. Yes, the original design leaves a lot
to be desired but it's what I have to work with.

The dates all reflect the same thing, an instance of a Hepatitis B
vaccination. The original program allowed users to input the dates into
anyone of 4 date fields. For the most part, they followed the design which
was to start at DATE1 and work through until DATE4. However, there are many
instances of sloppy date entry and the dates listed do not follow that
progression. Does this provide you enough info to assist?
 
If it were me, I'd probably create a new table with the proper structure.
Then, I'd run four append queries to add records to that table.

The first append query would pull all the records with an entry in the DATE1
field of your "bad" table. It would pull the SSN field and the DATE1 field.
It would then append a record for each to the "proper" table.

The second append query would pull all the records with an entry in DATE2
and append a record to the properly-structured tables.

Etc.

Once the data was in the properly-designed table, then, I'd build a query
to pull all the records from that table. I'd base a report off of this
query and I'd Group/Sort first on the SSN and then on the date. In my SSN
group header, I'd print the SSN (plus name, address, andy other info. Then
in the DETAIL section, I'd print the date.

The report would print them in order and would have one or more lines per
person, depending on how many shots they have gotten.

There are other ways, but that is the method I'd use.

Hope that helps,

Rick B
 
As Rick suggested you do have an embedded 1 to many relationship in the
table and may want to consider restructuring your table. It would make doing
this much easier. However, if you are stuck with it as is, you can use a
UNION query to normalize it.

SELECT SSN, Date1
FROM YourTable
UNION
SELECT SSN, Date2
FROM YourTable
SELECT SSN, Date3
FROM YourTable
UNION
SELECT SSN, Date4
FROM YourTable;

Then create a Top 3 Query from that.
 
Note that you don't have to pull this to a report; you could simply view/use
the query. Once you get the data in the "proper" table, you could build a
query that pulls SSN and date. Sort ascending or descending on each column
and you should get what you want...


452-68-9977 Dec 14, 2001
452-68-9977 Jan 12, 2002
458-12-0047 Feb 3, 2000
458-12-0047 Jul 10, 2001
458-12-0047 Mar 8, 2002
521-04-8739 Sep 20, 2000

etc.
 
Lynn always has cool ways to do in one query what takes me four or five
queries to do! Just goes to show you how flexible Access can be!

:-)
 
Back
Top