Query Madness

[mask2xxx]

Hi there,

To anyone and everyone that may be able to help. I have a database which is
currently being used to monitor the number of visits to various sites where I
work at. I obtain a file each month which contains registration info for
each site. I then import the reg data into Access and report the stats for
each site per month. I have been asked to report the number of registrations
for each site per hour of each day for the past year. The honchos at my job
like REALLY want to know how each site is doing. Is there a way to query
the information where I would be able to possibly create a table that would
show the number of regs per hr per a specified day or possibly a one shot
deal where I would be able to run the query for the entire year and obtain
the number of regs per day per hr for that year. Forgive the lenght of the
message but if anyone can assist or has a suggestion that I could use it
would be greatly appreciated.

thanks

 

[John Vinson]

Hi there,

To anyone and everyone that may be able to help. I have a database which is
currently being used to monitor the number of visits to various sites where I
work at. I obtain a file each month which contains registration info for
each site. I then import the reg data into Access and report the stats for
each site per month. I have been asked to report the number of registrations
for each site per hour of each day for the past year. The honchos at my job
like REALLY want to know how each site is doing. Is there a way to query
the information where I would be able to possibly create a table that would
show the number of regs per hr per a specified day or possibly a one shot
deal where I would be able to run the query for the entire year and obtain
the number of regs per day per hr for that year. Forgive the lenght of the
message but if anyone can assist or has a suggestion that I could use it
would be greatly appreciated.

thanks

The answer to your question is "Yes, probably; depends on the
structure of the data in the tables".

Since you haven't indicated how the data in the file is structured or
how you import it, I can't suggest HOW you would do it - but if you
have the registration time stored in a date/time field, a Totals query
of some sort would work. What you might do is create a Query using a
calculated text field defined as:

SortRegTime: Format([datetimefield], "yyyymmddhh")

This will have the registration time, to the hour; it can be used as a
Group By in a totals query to count registrations. You'll also want to
group by the site.

If you could describe your table structure we can probably give more
specific advice.

John W. Vinson[MVP]

 

[Guest]

Do you know what is more maddening?

When you tell a long story of what you are doing but never say anything
about how you are doing it.

Nothing about your table structure. How can anyone tell you how to extract
data if you do not say how it is stored? Post the field names and datatype.
Also include a sample of the data and how you would like the results
displayed.

 

[mask2xxx via AccessMonster.com]

Hi John thanks for your response if you can tell me what kind of info you
would need to help analyze my issue I would be more than happy to provide it
on the next message.

thanks again

Hi there,

[quoted text clipped - 13 lines]

thanks

The answer to your question is "Yes, probably; depends on the
structure of the data in the tables".

Since you haven't indicated how the data in the file is structured or
how you import it, I can't suggest HOW you would do it - but if you
have the registration time stored in a date/time field, a Totals query
of some sort would work. What you might do is create a Query using a
calculated text field defined as:

SortRegTime: Format([datetimefield], "yyyymmddhh")

This will have the registration time, to the hour; it can be used as a
Group By in a totals query to count registrations. You'll also want to
group by the site.

If you could describe your table structure we can probably give more
specific advice.

John W. Vinson[MVP]

 

[John Vinson]

Hi John thanks for your response if you can tell me what kind of info you
would need to help analyze my issue I would be more than happy to provide it
on the next message.

The name of your table; the names and datatypes of the relevant
fields; two or three records of sample data.

John W. Vinson[MVP]

 

[mask2xxx via AccessMonster.com]

Hi John,

Because of the senitive nature of the data stored in the database I won't be
able to provide you with a sample from the table but the table name is
Monthly Visit Stats 2005 and the fields are labeled as follows:

acct-num text
acct-type text
ev-dos Date/Time
ev-hhmm Date/Time - the time has
been formatted to use Long Time format
fac text
dept text
mrn text
pt-lastname text
pt-firstname text
extract-pt-race text
extract-pt-sex text
dob Date/Time
extract-pt-street-1 text
extract-pt-street-2 text
extract-pt-city text
extract-pt-state text
extract-pt-zipcode text
extract-pt-day-phone text
pt-ssn text
guarentor-num text
guarentor-lastname text
guarentor-firstname text
guarentor-middle-initial text
guar-street-1 text
guar-street-2 text
guar-city text
guar-state text
guar-zipcode text
guar-phone text

acct-num acct-type ev-dos ev-hhmm fac
dept
00000000 S 2/19/2006 12:42:00 PM BBBB CPED
00000000 S 2/19//2006 12:53:00 PM BBBB CPED

The list of fields are incomplete but as you can see its just basic patient
information data. Basically I just want to be able to tell the database show
me how many pts were registered per hr for specified day. If you could make
this work john your a saint.

 

[mask2xxx via AccessMonster.com]

Hi Karl,

Sorry for the lack of detail on the inital message I think this should be
helpful. Because of the senitive nature of the data stored in the database I
won't be able to provide you with a sample from the table but the table name
is Monthly Visit Stats 2005 and the fields are labeled as follows:

acct-num text
acct-type text
ev-dos Date/Time
ev-hhmm Date/Time - the time has
been formatted to use Long Time format
fac text
dept text
mrn text
pt-lastname text
pt-firstname text
extract-pt-race text
extract-pt-sex text
dob Date/Time
extract-pt-street-1 text
extract-pt-street-2 text
extract-pt-city text
extract-pt-state text
extract-pt-zipcode text
extract-pt-day-phone text
pt-ssn text
guarentor-num text
guarentor-lastname text
guarentor-firstname text
guarentor-middle-initial text
guar-street-1 text
guar-street-2 text
guar-city text
guar-state text
guar-zipcode text
guar-phone text

acct-num acct-type ev-dos ev-hhmm fac
dept
00000000 S 2/19/2006 12:42:00 PM BBBB CPED
00000000 S 2/19//2006 12:53:00 PM BBBB CPED

The list of fields are incomplete but as you can see its just basic patient
information data. Basically I just want to be able to tell the database show
me how many pts were registered per hr for specified day. I could use all
the help you can give, thanks so much.

Do you know what is more maddening?

When you tell a long story of what you are doing but never say anything
about how you are doing it.

Nothing about your table structure. How can anyone tell you how to extract
data if you do not say how it is stored? Post the field names and datatype.
Also include a sample of the data and how you would like the results
displayed.

Hi there,

[quoted text clipped - 13 lines]

thanks

 

[John Vinson]

Hi John,

Because of the senitive nature of the data stored in the database I won't be
able to provide you with a sample from the table but the table name is
Monthly Visit Stats 2005 and the fields are labeled as follows:

OUCH.

So it's a big spreadsheet, with the same patient info repeated row
after row, rather than a normalized set of tables? Is this information
imported from elsewhere, or maintained in Access? Might it be possible
to create a correctly normalized database?

Comments inline.

acct-num text
acct-type text
ev-dos Date/Time
ev-hhmm Date/Time - the time has
been formatted to use Long Time format

The format is ABSOLUTELY IRRELEVANT.

A Date/Time field is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. The format
controls what is displayed - not what's stored. I'd use a single
date/time field to store the date and time of the visit; it's
trivially easy to put two textboxes on a form to display the date and
time separately if you so desire.

fac text
dept text
mrn text
pt-lastname text
pt-firstname text
extract-pt-race text
extract-pt-sex text
dob Date/Time
extract-pt-street-1 text
extract-pt-street-2 text
extract-pt-city text
extract-pt-state text
extract-pt-zipcode text
extract-pt-day-phone text
pt-ssn text

This information would be better stored in a Patients table, with one
record per patient.

guarentor-num text
guarentor-lastname text
guarentor-firstname text
guarentor-middle-initial text
guar-street-1 text
guar-street-2 text
guar-city text
guar-state text
guar-zipcode text
guar-phone text

This information would be better stored in a Guarantors table.

acct-num acct-type ev-dos ev-hhmm fac
dept
00000000 S 2/19/2006 12:42:00 PM BBBB CPED
00000000 S 2/19//2006 12:53:00 PM BBBB CPED

The list of fields are incomplete but as you can see its just basic patient
information data. Basically I just want to be able to tell the database show
me how many pts were registered per hr for specified day. If you could make
this work john your a saint.

SELECT [ev-dos], Format([ev-hhmm], "hh") AS TheHour, Count(*) AS
CountOfPatients
FROM [Monthly Visit Stats 2005]
GROUP BY Format([ev-hhmm], "hh")
WHERE [ev-dos] = [Enter date of service:]

John W. Vinson[MVP]

 

[mask2xxx via AccessMonster.com]

Thanks John I'll give the query a try and get bk to u.

Hi John,

Because of the senitive nature of the data stored in the database I won't be
able to provide you with a sample from the table but the table name is
Monthly Visit Stats 2005 and the fields are labeled as follows:

OUCH.

So it's a big spreadsheet, with the same patient info repeated row
after row, rather than a normalized set of tables? Is this information
imported from elsewhere, or maintained in Access? Might it be possible
to create a correctly normalized database?

Comments inline.

acct-num text
acct-type text
ev-dos Date/Time
ev-hhmm Date/Time - the time has
been formatted to use Long Time format

The format is ABSOLUTELY IRRELEVANT.

A Date/Time field is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. The format
controls what is displayed - not what's stored. I'd use a single
date/time field to store the date and time of the visit; it's
trivially easy to put two textboxes on a form to display the date and
time separately if you so desire.

fac text
dept text

[quoted text clipped - 11 lines]

extract-pt-day-phone text
pt-ssn text

This information would be better stored in a Patients table, with one
record per patient.

guarentor-num text
guarentor-lastname text

[quoted text clipped - 6 lines]

guar-zipcode text
guar-phone text

This information would be better stored in a Guarantors table.

acct-num acct-type ev-dos ev-hhmm fac
dept

[quoted text clipped - 5 lines]

me how many pts were registered per hr for specified day. If you could make
this work john your a saint.

SELECT [ev-dos], Format([ev-hhmm], "hh") AS TheHour, Count(*) AS
CountOfPatients
FROM [Monthly Visit Stats 2005]
GROUP BY Format([ev-hhmm], "hh")
WHERE [ev-dos] = [Enter date of service:]

John W. Vinson[MVP]