Re: Date ranking
From: Rick B (Anonymous)
Date: 02/08/05
- Next message: Lynn Trapp: "Re: Date ranking"
- Previous message: MGFoster: "Re: Subqueries and Confusion"
- In reply to: Brett S.: "Re: Date ranking"
- Next in thread: Lynn Trapp: "Re: Date ranking"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Feb 2005 14:39:59 -0600
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
"Brett S." <BrettS@discussions.microsoft.com> wrote in message
news:89A1EB9C-B695-465E-9300-88AD61B66452@microsoft.com...
> 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?
>
> "Rick B" wrote:
>
> > 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
> >
> >
> > "Brett S." <BrettS@discussions.microsoft.com> wrote in message
> > news:7250EC3E-E7BB-4166-9E51-7FB34816D9F2@microsoft.com...
> > > 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
> >
> >
> >
- Next message: Lynn Trapp: "Re: Date ranking"
- Previous message: MGFoster: "Re: Subqueries and Confusion"
- In reply to: Brett S.: "Re: Date ranking"
- Next in thread: Lynn Trapp: "Re: Date ranking"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
