Barry-
Is it clear to you that your table is badly designed? Those dates are a
repeating group.
To answer your question, use a UNION query:
SELECT [Account Number], … , [8 AM ET], [Date 1] As TheDate
FROM MyTable
UNON ALL
SELECT [Account Number], … , [8 AM ET], [Date 2] As TheDate
UNON ALL
SELECT [Account Number], … , [8 AM ET], [Date 3] As TheDate
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
---------------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Barry White
Sent: Wednesday, May 02, 2012 10:08 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Excel, off topic, transpose and copy down?
I have the following data:
Account Number Age Age Group Medical/Surgical Patient Type 8AM pass
through Before1/After0 8AM PET Before0/After1 8AM ET Date 1 Date 2 Date
3
123456789 0 <18 MED I 3 1 1 10/21/2011 10/22/2011 10/23/2011
234567890 0 <18 MED I 3 1 1 10/09/2011 10/10/2011 10/11/2011
GIVEN the number of dates in each row (date 1 -3) in this case, and
there could be up to 28 date columns---,
I need exactly that many total rows (3, one for each date), with the
account number, age, age group, med/surg, patient type, 8AM pass
through, Before1/After0 8AM PET, Before1/After0 8AM ET columnar data's
info repeated, and the date being reduced to one column, like so--
Account Number Age Age Group Medical/Surgical Patient Type 8AM pass
through Before1/After0 8AM PET Before0/After1 8AM ET Date 1
123456789 0 <18 MED I 3 1 1 10/21/2011
123456789 0 <18 MED I 3 1 1 10/22/2011
123456789 0 <18 MED I 3 1 1 10/23/2011
234567890 0 <18 MED I 3 1 1 10/09/2011
234567890 0 <18 MED I 3 1 1 10/10/2011
234567890 0 <18 MED I 3 1 1 10/11/2011
Exactly how do I do this? I cannot figure this out.
Any expertise would be greatly appreciated.
I have to run a census report based off certain statistics, and those
repeat/unchanging columns are the certain stats. The specific report I am trying
to run is a census report, and that is why each unique date/account number combo
needs its own row. The database I pull this info from only contains the
beginning and ending time stamps as to when the patient is in a given location
of the hospital. When you wish to do a 11AM census for example, there would be
two ways to do it that I could think of,
1. a snapshot in time, calculating all patients that cross or enter a location
on a certain very second, say exactly 11AM, or 3PM.
2. or say a true hourly census, for example, 11AM meaning, all patients that
enter or hit a location on or across any time point within 11:00:00 AM through
11:59:99.9999/100ths of a second.
As you can imagine in the first method example, what if the patient entered a
location at 11:04 am and left at 11:58 am? this patient would not get counted in
the 11AM "hourly census" nor the noon census.
In any event, the data base only gives me time stamp end points, beginning time
patient entered a location, and ending time they left and maybe were transferred
elsewhere within the hospital, but not in that location anymore. I figured out
how to parse the multiple dates for the given hourly time stamps that it crosses
each day, since it will be a correct hourly/daily census count now.
IT is the department that is using method #1 right now, and I can see no value
to undercounting patients, especially since a census is all about counting
correctly. So far, I have figured out the correct answer, I just need to parse
the date column into a single column rather than multiple and copy the rest of
the stat info down dependent upon how many many unique dates I have given the
unique patient and visit combo. In other words, if a patient stayed in a given
location from 4/2/2012 at 6:45 am to 4/11/2012 at 5:45am, if I were doing an 8AM
census, then that patient crossed that time stamp 10 times, 4/2-4/10/2012.
Eric Lutz
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar