Results 1 to 2 of 2

Thread: Querry Help

  1. #1
    Join Date
    Jan 2014

    Unanswered: Querry Help

    We need to develop a database that tracks everyone that visits our food pantry. The agency that we get our food from is now requiring us to track: Total number of families served, Total number served, Total number with disability; Total Male, Total Female and age break down (Senior, Adult, Child). I have the basic table set up so that is not a problem and I can do a simple querry to get the number of families served.

    I set up a table for each week that lists each families ID number (1-1,000) and we check off the families that show up. I can then link the Master Table and the Date Table and querry by date, but it only pulls the first person listed with the family id.

    How can I set up a querry that allows me to only put the date with the family id number, but allows me to pull all the data associated with each family member sharing that id number?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I think your table design is suspect. what I'd strongly recommend is that you move to have one table that records all servings by date not one table per week. the reason?
    well if the supplier wants figures by the month?, or compare 1st week this month with 1st week last month, or numbers by month over the last n months and so on.
    Also by having one table per week it mean there is a maintenance task to create the new weekly tables, that means you either cant use forms/reports easily OR you have to changs those every week

    there's not enouygh detail in waht you have said so far but for this sort of problem I'd expect
    1) a table of 'persons' identfying names, and whatever other information you need to identify a person, included in this is an indentifier that indictaes who the family principal is. that identifies which family this person 'belongs' to, and form that you know the the number of families
    2) a table containing details of servings (essentially the date and time (all one column) of the serving and the family principal as the primary key). Id make the primary key the serving date and the person ID... you could change that into a date if you only do one serving per day (if so use date() not now()) or use date+personId+a serfving ID if you do multiple servings per day

    I'd probably want to add other tables such as an age category table sindicating the sort order. this table would have to include some form of upper and/or lower limit for each age category and the sort sequence (effectively the ID so you can alter the sequence as required). the advanatge of this approach is if the rules for age grouping change you cana make changes to the table and those changes will ripple through the reports

    I'd also want to add a calendar so that you cna map actual dates to man made dates such as accouting years+periods. you can use the datepart function [DatePart(“ww”,mydatecolumn)]

    however the details of the implementation come down to how your operation works. so whether you issue food to the family principal, or whether you serve food to indviduals, whether you serve to the principal and count the dependants or just issue to the principal and hope the right number of dependants actually get served.

    you have two competing drivers
    accuracy of information (meaning more effort to record data) and speed of data collection
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts