Query [Dates] over Several columns

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

Guest

I need someone to point me in the right direction. I work for a charity where
people are allowed to come in only 4 times per year, so the LbsFood under
Visit1Date for one person could be the same as Visit2Date for another, etc.
How can I query to find the pounds of food given for a particular date when
the date could be in one or all of 4 columns? Any guidance would be greatly
appreciated.
 
Just put your date criteria under each column but put it on a different row
(to create an OR condition).

If Visit1Date = somedate *or* Vistit2Date = somedate... etc.

I would probably prompt the user for the desired date and that will allow
them to enter it once when the query is run.

in your criteria under Visit1Date, just put...

=[EnterDate]

Then under Visit2Date (but on the next line) put...

=[EnterDate]


etc.
 
Your data structure is mis-designed. The best fix would be to change the
structure so you had a separate table to record the visits and the amounts
of food. The tables might look something like

TblPersons
PersonID
PersonName
(Other person information that you store)

TblVisits
PersonID (Fld pointing to a table of persons using the service - that is one
of the values that is stored in tblPersons
VisitDate: (if visit date is an actual date)
VisitNumber (if needed)
PoundsDistributed

Then your query would be simple for any date, you could get totals pounds,
avg pounds, number of people, etc. Also, if the policy changes and people
are allowed 6 visits or 12 visits a year, you don't have to change anything
in the table structure.

IF you are stuck with your present structure, you will need to use a UNION
ALL query to get your data into the right structure and then you can query
against this query. You can only build this type of query in

SELECT PersonID, VisitDate1 as Visited, lbsFound1 as PoundsDistributed
FROM YourTable
UNION ALL
SELECT PersonID, VisitDate2, lbsFound2
FROM YourTable
SELECT PersonID, VisitDate3, lbsFound3
FROM YourTable
SELECT PersonID, VisitDate4, lbsFound4
FROM YourTable

If you save that as query qUnionFoodDistro you can use it to get your
information. Just bring it into the query grid and use it as you would a
table.

In SQL view you could get your total for a specific date using something
like:
SELECT Sum(PoundsDistributed) as TotalPounds
FROM qUnionFoodDistro
WHERE Visited = #12/31/2005#

For each day of a month period
SELECT Visited, Sum(PoundsDistributed) as TotalPounds
FROM qUnionFoodDistro
WHERE Visited Between #12/1/2005# and #12/31/2005#
GROUP BY Visited
 
Back
Top