Query Design help for Food Pantry dbase

[gr8rishe04]

I am fairly proficient in creating databases; however, I am a bit of a novice when it comes to intermediate/advanced query building. Hope someone can help.

I have a database that is used to keep records of our food pantry clients. The database includes name and address information, in addition to household size, ages of children, and individual dates when food was given.

I am trying to come up with a query (or formula?) that will provide me with the number of households that received food in a particular month. There may be other queries I would like to do on the dates at a later time, but the problem I have is figuring out the syntax.



Each date field is separate because some households may have visited our food pantry once, some five times, others 20 times, etc. So, I'm thinking in my mind that I would need to do some kind of IF statement, like:

IF Dates_Food_Given + Dates_Food_Given2 + Dates_Food_Given3... + Dates_Food_Given30 = '11/01/2010' thru '11/30/2010' then count.

I know the syntax is not correct, but that is part of my problem; I'm not familiar with the proper syntax. I am VERY rusty in this regard. So, I hope I wasn't too unclear in my explanation. I just am not sure if I need to type out all of the date fields, which could be very cumbersome since there are 30 of them and if I need to include anything else in my formula

Any guidance would be greatly appreciated!

[LillMcGill]

You are feeling the effects of having a database that is not normalized. Sorry, but please consider a remake of the database. Having 30 date fields that should be one field is always going to be a continual source of stress, and will make everything 10 times harder than it should be.

You undoubtedly need more tables than this, but at a minimum, you should have a table for Household.. HouseholdID, PrimaryLastName, etc... (in order for a field to go into this table, it must be a fact about a household where no household could ever have more than 1 that you care to track).
And a seperate table for Visits... VisitID, HouseholdID, DateOfVisit

[gr8rishe04]

Thank you very much for your response, LillMcGill. I initially started out with three different tables - one for primary info (name & address), one for household info (other adult and/or children's names ages, etc.) and then a separate table for the dates food was given; however, after trying to grapple with table relationships, how to tie one household to multiple visits, and trying to remember how to tie all the info together to get the desired results, I was losing a lot of time which I couldn't afford. So, yes, I do have a "flat" database which I'm sure, as you pointed out, is not very efficient.

Is there ANY way possible to get the information I am seeking without having to rebuild my whole database structure?

In your post, you mentioned that:

"(in order for a field to go into this table, it must be a fact about a household where no household could ever have more than 1 that you care to track). And a seperate table for Visits... VisitID, HouseholdID, DateOfVisit". If I am understanding you correctly, I don't think this would work for me because we have people in our database with the same last names; we even have two different people with the same first and last name. Also, I'm not sure if I previously stated it clearly, but each household could have as many as 10 or 20 visits to our pantry. So, how could I keep track of multiple visits from one household with the structure you suggested? Thanks again for your help!

[LillMcGill]

I don't think this would work for me because we have people in our database with the same last names; we even have two different people with the same first and last name.

Everybody's database has that.

how could I keep track of multiple visits from one household with the structure you suggested?

Visits go in a table about visits. Each visit creates a new row in the table about visits.

Is there ANY way possible to get the information I am seeking without having to rebuild my whole database structure?

I don't know.

[TheShabz]

You really won't be able to pull this off without remaking the db. I would have the following tables

tblHouse
HouseID (PK)
address1
address2
etc

tblResident
ResidentID (PK)
HouseID (FK)
isPrimary (important, yes/no field)
name
age
etc

tblFood
foodID (PK)
foodName
etc

tblVisit
VisitID (PK)
HouseID (FK)
ResidentID (FK, optional. if you want to include who got the food, go ahead)
date
etc

tblVisitFood
VisitFoodID (PK)
VisitID(FK)
FoodID(FK)

So yea, PK = Primary Key, FK = Foreign Key. the relationships should be easy from there. Your query WHERE clause would be
WHERE tblVisit.Date Between #date1# and #date2#