Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining queries (Office 2000)

    I have a database, which consists of three tables and several queries based on those tables. One table consists of all the entrants that will be participating in a fishing tournament. The name and address, the boat, entry fee, additional anglers, etc. The entry fee includes dinner on Friday, Saturday, Sunday and breakfast on each day. Additional dinners / breakfasts can also be purchased. Also included are T-shirts. I have a query that computes the number of dinners etc based on the entry fee. This query works well.

    The second table is for the Sponsors. Again, name, address etc. Their sponsorship also includes dinner tickets for Friday and Saturday. I have a query that computes the number of dinners etc based on the type of sponsorship. This query works well.

    What I need is a query that combines the two so that I can do a report that will give the combined number of tickets for each day etc. I tried doing a third query based on the other queries but I get an error stating that I have
    Louise

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

    Re: Combining queries (Office 2000)

    One option would be to start with a union query that lists records from the entrants and sponsors tables (you haven't told us what the third table is, so I don't know whether it needs to be included as well), then create a query based on the union query.

    Without knowing anything about the structure of the queries you have, it is impossible to tell what is possible with them and what isn't.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining queries (Office 2000)

    Hi Hans,
    Don't know about a Union query. Will have to look into that. The third table is for additional tickets sold at the event itself. I have attached (hopefully) a stripped down version of the database. I think that should make it easier.

    Thanks
    Attached Files Attached Files
    Louise

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

    Re: Combining queries (Office 2000)

    Thanks, now I know at least what you're working with. Can you explain what you want to combine and how? At first glance, the queries don't seem to have much in common (but that's no doubt because I'm not familiar with the context).

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining queries (Office 2000)

    Good morning Hans,
    You're right. The only thing they have in common are the dinner tickets / sales. The participants in the fishing tournament recieve dinner tickets as part of their entry, the Sponsors (depending on their level) also receive dinner tickets. We also sell addtional tickets (and hats) at the night of registration.

    I need a report that combines the total number of tickets given out and sold and what they represent in income. In other words, how many tickets were given out for Friday, Saturday etc. including the number that were sold. I also need a report that would give me a total revenue from the entire event including entry fees, sponsorship etc.

    Hope this helps and thanks once again.
    Louise

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

    Re: Combining queries (Office 2000)

    See if you can use the queries in the attached version as starting point. I created totals queries for the registration data and sponsors that sum the number of tickets. Since these queries return only one record each, you can then create a query that combines them.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining queries (Office 2000)

    Hi Hans,
    That looks like it could work. Thank you.

    Question, Are they regular queries? I noticed that the Combined query has an asterisk at the end (qryRegistrationTotals.* ) in the field section. Does that mean something? Also "qryRegistrationTotals" and "qrySponsorTotals" are listed under "table" and as you know, I don't have those names for tables. Sorry for all the dumb questions.

    Thanks again Hans!
    Louise

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

    Re: Combining queries (Office 2000)

    qryRegistrationTotals and qrySponsorTotals are Totals queries, i.e. regular select queries in which View | Totals has been selected.

    qryRegistration.* is simply a shortcut for "all fields from qryRegistration". If you look at the list of fields in the upper half of the query design window, you'll see that the first item in the list is an asterisk *. If you double click this, or drag it to the query grid below, you'll see that qryRegistration.* is inserted.

    The Table row in the query design grid can hold the name of a table or of a query. So formally, the caption should have been Table/Query, but I think Microsoft settled on Table to make the interface less cluttered.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining queries (Office 2000)

    Thanks Hans!
    Louise

Posting Permissions

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