Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query on 12 Tables (Access 2000)

    I have a db with 12 tables. A table with supplier transactions for each month of the year. I would like help on writing a query to report the transactions for a supplier for the year.

    Refer Attached

    Thanks

    Mario
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query on 12 Tables (Access 2000)

    You can bring the information for all the tables together using a Union query.

    However, you should seriously reconsider redesigning your database. All your transaction information should be in a single table. The arbitrary splitting of information into multiple tables based on dates, etc., invariably causes more trouble than it solves.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query on 12 Tables (Access 2000)

    Please give me an example of using the Union query on the sample db.

    My problem with the 12 tables are that the table each is around 800 000 entries. How will I go about combining the tables?

    Thanks

    Mario

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

    Re: Query on 12 Tables (Access 2000)

    I have supplied your changed database for you, you will notice the union query.
    It is easy to add to, all you need is to add the words 'UNION ALL' at the end of the current query (remember to delete the semicolon before you do it) and put the next SELECT statement after that.
    Then just keep doing the same thing again for each table. A UNION query could have up to 49 unions in Access 97.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query on 12 Tables (Access 2000)

    800,000 records in each table? That might be a good reason to keep them separate in Access, although with that kind of size, I'd think the moving to SQL would be advisable.

    A union query is rather simple. In your case it would be:
    Select * from tblJanuary Union Select * from tblFebruary Union etc, etc. etc!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query on 12 Tables (Access 2000)

    What does the pa mean and how will I change the query to allow a inputbox for the user to supply a suppl no

    Thanks for all the help

    Mario

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

    Re: Query on 12 Tables (Access 2000)

    Hi Mario,
    The PA is there as an alias for the table, it's just to make the query more readible, imagine if you had [2002-03 Pay Adv]. before each variable name.

    The query could look like:

    PARAMETERS [Enter SUPPL No] long;
    SELECT PA.[Suppl No], PA.Period, PA.[Doc Type], PA.Amount
    FROM [2002-03 Pay Adv] AS PA
    WHERE PA.[Suppl No] = [Enter SUPPL No]
    UNION ALL SELECT PA.[Suppl No], PA.Period, PA.[Doc Type], PA.Amount
    FROM [2002-04 Pay Adv] AS PA
    WHERE PA.[Suppl No] = [Enter SUPPL No]
    ORDER BY PA.[Suppl No], PA.Period;

    Or you could supply the Suppl No via a form variable if driving the report from a form, eg the WHERE clause would be

    WHERE PA.[Suppl No] = forms![your form name]![lngSuppl No]

    and lngSuppl No is the name of the supplier number control on the form.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query on 12 Tables (Access 2000)

    Excellant, it makes a lot of sense. Thanks a lot

  9. #9
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query on 12 Tables (Access 2000)

    Another question.

    I need to do changes (amount to be divided by 100) to all 12 tables and select other criteria as well. Now will it be better to first do the extract (join) and then all the changes or to change the 12 tables first and then run the join?

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

    Re: Query on 12 Tables (Access 2000)

    If you want to update (modify) values, you must do it in each of the tables separately, because a union query is not updatable.

    If you want to have a calculated field like Amount2: [Amount]/100, it's more efficient to create a new query based upon the union query, and add the calculated field to the new query.

Posting Permissions

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