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

    Access report (access 2000)

    you guys have helped me out before and I am hoping you can point me in the right direction now. The center's database has related tables for each service that we might provide for a family. Persons are entered in one table that is related to their family so each person has both a personal ID and a family ID. Material goods that are given to persons are all kept in one table "aid" ; groups and classes that are attended are in another table as is the library check out table. Because Christmas is such a huge undertaking, we have had a separate table for each year and that is likely to cause me some trouble. I want a way to get data each year (want to be able to type in the beginning and ending dates of the year) showing each person that has received a service and to list all the services that they received for the year. Can you give me a hint as to a query that will pull this information?

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

    Re: Access report (access 2000)

    Hi ginger,

    Does the "aid" table contain a date/time field you can use for the selection, or a number field that stores the year? What is the name of the field?

    Do the "Christmas" tables also store "aid" information? If so, what are the names of the Christmas tables?

  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 (access 2000)

    each table has entry field for dates-- aid has date entered each time the person receives something material (food, clothes, gas, money for something etc) in groups, the date is entered each time the person attends; in Christmas the date is entered when the child is entered on this years list-- I may have really caused myself a problem since each Christmas year is a separate table-- there is SOOO much information entered at Christmas that it would be difficult to include all the years in one table-- last year 1588 children received gifts. In each of the tables; there is a link by the personal id number-- I tried to join all the Christmas data for each child into one query-- but by putting all the years as tables; only the 61 children who had received gifts all 4 years (I tried starting with 1999) were included -- I used OR as the criteria but-- but all except those 61 were excluded. I hope I am explaining this correctly.

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

    Re: Access report (access 2000)

    What you (probably) need is a union query.
    1. <LI>Start by creating a query based on only ONE of the tables, for example on the "aid" table. Add the fields you will need for the report to the query grid, and make sure to include the Personal ID field and the date field.
      <LI>Repeat step 1 for the other tables you need, so you will temporarily end up with a lot of queries. IMPORTANT: All queries should contain the same number and types of fields in the same order.
      <LI>Open the first query in design view an switch to SQL view (View | SQL). You will see something like

      SELECT aid.PersonalID, aid.SomeField, aid.OtherField
      FROM aid;

      with, of course, the appropriate names instead of these dummy names.
      <LI>Remove the semicolon at the end, and type UNION followed by a space into the empty line below.
      <LI>Leave this query open, and open the next query in design view, then switch to SQL view.
      <LI>The entire SQL will be highlighted. Copy the text to the clipboard (Ctrl+C).
      <LI>Switch to the first query and paste (Ctrl+V) after the space after UNION.
      <LI>Switch to datasheet view to check that it works, then switch back to SQL view.
      <LI>Repeat steps 4 - 8 for the other queries, except that you should NOT type UNION after the last one.
    You should now have something like this:

    SELECT aid.PersonalID, aid.SomeField, aid.OtherField
    FROM aid
    UNION SELECT christmas99.PersonalID, christmas99.SomeField, christmas99.OtherField
    FROM christmas99
    UNION SELECT christmas00.PersonalID, christmas00.SomeField, christmas00.OtherField
    FROM christmas00

    with the appropriate names, and with the correct number of contributing queries. If the union query works OK, save it.

    You can now create a new query based on the union query to select records from a specified period of time, and use this as record source for your report.

    Post back if you need more assistance.

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

    Re: Access report (access 2000)

    have never tried to use a union query-- thank you for the lesson-- I will try the whole thing at the office tomorrow-- and will let you know how it works-- ummm is there any limit to the number of unions? Christmas data goes back 5 years in the current form-- so would have those 5 plus aid plus groups.classes plus library

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

    Re: Access report (access 2000)

    You can include many queries in a union query.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access report (access 2000)

    I found there was a limit of 49 Selects in a union query in Access 97. I don't know if it's been increased in later versions of Access.

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

    Re: Access report (access 2000)

    Yes, there is a limit, but I sincerely hope Ginger doesn't need that many tables in the query.

  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 (access 2000)

    LOL do not need that many queries; BUT there is a problem-- the person table does not have dates-- the entry and update dates are held in the ":family" table and it has never been a problem since I connected the two files in a query when I wanted a person's info based on date-- I am thinking it wont matter on this union query since I dont actually need a date for the persons other than the dates in the data about them ie when they attended a group or class. However, Hans did say that each query had to have the same number and type of data---- so is there another answer, or do I need to add a date field to person? certainly could do update query and get it moved from the family file-- but there are 13000 + entries in the database and hate to have the data stored there twice--- and it would be rather inconvenient to have to continue to update it. Any suggestions?

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

    Re: Access report (access 2000)

    Do you need the persons table in the union query? I thought you had other tables to keep track of services rendered.

    If you need details about the persons, you can build the union query first, without including the persons table, then create a new query based on the union query, the persons table and perhaps the families/households table, joined on PersonalID.

  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 (access 2000)

    of course! some days my head is simply not attached-- will try all this at the office today and report back-- thank you

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

    Re: Access report (access 2000)

    well, I have done it worng-- I will submit it here for you to look over and I will copy it to take home and work on over the weekend-- will go back and see what infomation I can retrieve from the query without adding the person


    SELECT [Person (new)].[Personal ID], [Person (new)].[Family ID], [Person (new)].[Full Name], qryForUnion2000hristmas.EntryDate, qryForUnion2000hristmas.Gift1
    FROM [Person (new)] INNER JOIN qryForUnion2000hristmas ON [Person (new)].[Personal ID] = qryForUnion2000hristmas.PersonalID
    WHERE (((qryForUnion2000hristmas.EntryDate) Between [ENTER BEGINNING DATE] And [ENTER ENDING DATE]));

    will recheck the type of data in the libray material id to be sure it is text-- that could be the problem

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

    Re: Access report (access 2000)

    I don't know what the query qryForUnion2000hristmas does, and you don't say what goes wrong <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Posting Permissions

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