Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access report (2003)

    Here I am asking for help again. We are trying to get accurate reports for USDA food. When families come in to request assistance we gather some information and we note the aid that is received. We enter "aid" by the person receiving the aid rather than by the family. (sometimes a family member is not with the family for a while and while technically a member of the family, did not receive food) When I print a report, I need to count all the people and count the number of families helped. I am wondering if the query I am using is discounting a family if they get food in January and then again in March due to counting the family only once. If so --is there a way to get around this? Thank you for all the help you always give to me. Here is the query: SELECT tblAid.[Item received], tblPerson.[Family ID], Count(tblPerson.[Personal ID]) AS [CountOfPersonal ID], tblAid.[Date Received], tblAid.[Date Received]
    FROM tblAid INNER JOIN tblPerson ON tblAid.[Personal ID] = tblPerson.[Personal ID]
    GROUP BY tblAid.[Item received], tblPerson.[Family ID], tblAid.[Date Received], tblAid.[Date Received]
    HAVING (((tblAid.[Item received]) Like "*food*") AND ((tblAid.[Date Received]) Between [start date] And [end date]));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report (2003)

    Why is the Date Received included twice in the query? You need only one instance.

    This query groups by (among other fields) date, so you'll get an individual record for each date. Don't you want to count records for the whole period from Start Date to End Date? If so, set the Total option for the date field to Where.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    Truthfully, I dont know why it is there twice-- thank you-- for some reason I forget to use WHERE -- now, I know that this will give me the information for each month witout difficulty and it will break down the quarters by month--BUT I think that if a family gets food in one month they will not show again if I try to print a whole quarter or even the whole year. I will experiment with it tonight-- thanks for all you do for this group-- more times than I want to count you have saved me when I needed help.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    OK tested the query both ways-- when the second date field is removed, I lose families who received food more than once in a month-- so I guess it is actually picking up the ones who received something in a bigger time frame.-- I wish I was more skilled in writing queries and reports-- but I keep plodding forward. I will remove the second date field when the end of year comes and I want to find only the folks who received service without counting them for more than one service. I know that this data base is rather primative for most of the readers here; but it does track over 16000 persons and many more services.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report (2003)

    It doesn't matter whether your database is "primitive" or not - we're happy to help you if we can.

    Could you provide more information about the tables, or perhaps attach a stripped down copy of the database? See <post#=401925>post 401925</post#> for instructions on how to remove unneeded items.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    well, I tried-- did a sample database--left out a lot of the old tables and queries (pertaining to Christmases past) and stripped all the data --added new made up data (all our data is confidential) and zipped it-- 267kb-- sooo tried to make it into a 97 file but ran into many problems--so thank you for trying--but it looks like it is not coming your way.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report (2003)

    You could create a blank new database and import only the necessary tables (with the made-up data) and query/queries - I don't think you need to include any forms, reports or modules. See if you can get that zipped into less than 100 KB (if you still want to try - that's up to you, of course)

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    I did do that but maybe you dont want to see so much of the database--will try again-- leaving out big parts of it

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    here is a very skimpy version-- the forms wont work but you would be able to tell that there used to be tables for drop down lists-- Ihope I have left enough for you to tell what I am trying to do--oh oh -- it wont up load to you-- although it is 100k-- perhaps it has to be just under?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report (2003)

    As I said, we probably don't need any forms for this problem, so you can remove them.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    here is the small version

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report (2003)

    Thanks. This will enable us to test things.

    Now what exactly do you want to accomplish:

    a) The number of unique families whose members received food in the specified period (i.e. if the Jones family receives food once, and the Smith family twice, the count of families is 2)
    [img]/forums/images/smilies/cool.gif[/img] The number if families whose members received food in the specified period, where each issue counts as one (i.e. if the Jones family receives food once, and the Smith family twice, the count of families is 3)
    c) Something else - please explain.

  13. #13
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    I need to do both during the course of a year-- on a monthy and quarterly basis-- I need to count to count the family each time it receives a food box and need to count each person for each visit-- on a yearly basis (to report to United Way) I need to only count the person once for the whole year no matter how many services he receives during the year-- I think that the first query is counting them (the family once for each service even though there are many persons in the family) and the second query (without the second date reference) will count the families only once for the time period-- will it count the persons only once? When the yearly reporting comes, a new challange happens because other services like Christmas gifts and groups and classes are not in the aid table. Possibly they should have been in a "services" table and divided by type of service? Sorry for the multiple questions-- everyone who gives money to a charity wants reports in a different format and wants different answers. My job is actually to administrate and teach but there is no one else here who can manage the computers so it is also my job. LOL I do this better than when I have to put on an accountant hat and figure the taxes. (did you understand that we are a very small agency?) Thanks for any suggestions you can make. The fact that the names for everyone are in the computer twice is that I need to group by family name part of the time but if I have the computer put the first and last name together for me rather than to enter them, I can not have it a unique field and I need the unique field to verify we are not dupicating entries.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report (2003)

    See if the attached version helps. I have added a series of queries, a form to make entering the date parameters easier, and a sample report that displays:
    - Number of persons aided (each time counts)
    - Number of unique persons aided
    - Number of unique families aided

  15. #15
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report (2003)

    I love the ease of using the form to gather data and show the report-- but when I do the query that you sent; and compare it to the old query-- the number of families is much higher (I would like to think the new figures are correct, but am concerned that although the number of persons is completely the same for every month, the family numbers are all higher) I cant figure out how your query differs. I would be grateful if you could explain it to me--then I will use the work you have done to do another one for total aid.

Page 1 of 2 12 LastLast

Posting Permissions

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