Query more that one field

[Stan]

I am running in ACCESS 2003 a database with a single table. It
records service rendered to clients of a food pantry. As each client
is served the date is entered into 1-6 fields SvcDate1, SvcDate2 etc.
I would like to query certain data for monthly periods. The date of
the month may appear in any one of the six fields. I now am running
the query for each of the fields separately. Is there any way I can
write a query that will respond to the dates in any and all of the
service date fields?

I understand the dates should have been in a separate table but I
inherited this application and am trying to make the best of it.

Thank you,
Stan Hanna

[inkman04]

On May 8, 2:10 pm, Stan <stanha...@hotm ail.comwrote:

I am running in ACCESS 2003 a database with a single table. It
records service rendered to clients of a food pantry. As each client
is served the date is entered into 1-6 fields SvcDate1, SvcDate2 etc.
I would like to query certain data for monthly periods. The date of
the month may appear in any one of the six fields. I now am running
the query for each of the fields separately. Is there any way I can
write a query that will respond to the dates in any and all of the
service date fields?

I understand the dates should have been in a separate table but I
inherited this application and am trying to make the best of it.

Thank you,
Stan Hanna

Hello Stan,

Perhaps one way you could do this it to enter the following into
the criteria for each field: [Enter Date]. Just make sure that
it is placed on the next line down for each "or" line. Just enter
the date your searching for once when the query in run and
you should get the lines that correspond to your date.

Also, if getting a result based on a form, enter the path
of the control with the date to be searched in the same way.

Example: Forms!frmNameHe re.ControlNameH ere
while placing it on each or line for each field.

Good luck.

[Rich P]

Hi Stan,

Your scenario sounds like you have an unNormalized DB - serveral date
columns in one table. The (simplified) rule for normalization is that
you have one column for each datatype in a table - the detail table that
is. So your detail table would look something like this:

DayOfService ServiceDate
svcdate1 1/1/07
svcdate1 2/1/07
svcdate1 1/19/07
svcdate2 ...
svcdate2
svcdate3
svcdate3
svcdate2
svcdate1
svcdate1
This way you can easily query on any given DayOfService for a range
ServiceDate Between date1 and Date2.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Stan]

On May 8, 7:01 pm, inkman04 <raycai...@yaho o.comwrote:

On May 8, 2:10 pm, Stan <stanha...@hotm ail.comwrote:

I am running in ACCESS 2003 a database with a single table. It
records service rendered to clients of a food pantry. As each client
is served the date is entered into 1-6 fields SvcDate1, SvcDate2 etc.
I would like to query certain data for monthly periods. The date of
the month may appear in any one of the six fields. I now am running
the query for each of the fields separately. Is there any way I can
write a query that will respond to the dates in any and all of the
service date fields?

I understand the dates should have been in a separate table but I
inherited this application and am trying to make the best of it.

Thank you,
Stan Hanna

Hello Stan,

Perhaps one way you could do this it to enter the following into
the criteria for each field: [Enter Date]. Just make sure that
it is placed on the next line down for each "or" line. Just enter
the date your searching for once when the query in run and
you should get the lines that correspond to your date.

Also, if getting a result based on a form, enter the path
of the control with the date to be searched in the same way.

Example: Forms!frmNameHe re.ControlNameH ere
while placing it on each or line for each field.

Good luck.

Sounds like a good suggestion. I will have to try playing around with
it. First attempt worked for the first two service dates but some
problems with the following. I will work on it and with you
permission be back to you will further questions.
Thanks
Stan

[King Ron]

If you are stuck with the non-normalized structure, you can create a
UNION query that will join the identically structured output from
several subqueries into a single result set.The UNION query can then
be used as the source for your search.

A UNION query is read-only. Also, you can't use the graphic query
builder to create a union query, you have to do it in SQL view. I
usually build the first pass/extract in the query builder, then switch
to SQL view and copy and alter the structure for each remaining pass.

<uncompiled SQL>

SELECT svcClientName as Name, svcClientID as ClientID, svcDate1 as
svcDate FROM aServiceHistory WHERE svcDate1 Is Not Null
UNION
SELECT svcClientName as Name, svcClientID as ClientID, svcDate2 as
svcDate FROM aServiceHistory WHERE svcDate2 Is Not Null
UNION
SELECT svcClientName as Name, svcClientID as ClientID, svcDate3 as
svcDate FROM aServiceHistory WHERE svcDate3 Is Not Null
UNION
SELECT svcClientName as Name, svcClientID as ClientID, svcDate4 as
svcDate FROM aServiceHistory WHERE svcDate4 Is Not Null
UNION
SELECT svcClientName as Name, svcClientID as ClientID, svcDate6 as
svcDate FROM aServiceHistory WHERE svcDate6 Is Not Null
ORDER BY Name, SvcDate;

</uncompiled SQL>

Save that query. Create a second query that uses the UNION query as
the data source, and you can then sort/search/filter on any of the
fields in the UNION.

Good luck!

Ron, King of Chi

On May 8, 5:10 pm, Stan <stanha...@hotm ail.comwrote:

I am running in ACCESS 2003 a database with a single table. It
records service rendered to clients of a food pantry. As each client
is served the date is entered into 1-6 fields SvcDate1, SvcDate2 etc.
I would like to query certain data for monthly periods. The date of
the month may appear in any one of the six fields. I now am running
the query for each of the fields separately. Is there any way I can
write a query that will respond to the dates in any and all of the
service date fields?

I understand the dates should have been in a separate table but I
inherited this application and am trying to make the best of it.

Thank you,
Stan Hanna