Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    London, Kent, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query a Query. basic stuff (Access97)

    Ok, <-- n00bie #54692 here....

    I'm pretty new to access, so this might not come across correctly..

    I know how to make a single query based on a table, filter it to give certain answers. I know how to make a crosstab query, and produce reports from these.

    What i just can't seem to work out, is how to make a new query, that queries two existing queries. I can see how to add the query to the panel above the data fields,so that i can see the 2nd queries fields.
    IF i attempt to add them to this query, it affects the 1st queries results.
    At the moment i have 2 separate crosstab queries, taking information from two single queries.

    I need 1 final report, that takes the data from both of these queries. plus some data from a field entry, and does some math...

    for example:

    Cross tab query 1 at location A, 1 on the report has the number 35 (days holiday)
    Crosstab query 2, at location A, 1 on the report has the number 12 (people)

    The report that i need to produce, is another crosstab query using these numbers, and all the other numbers.

    so using the numbers above, if i create an input field, and someone enters the number 145

    then in the final report i need to do say this.

    12 (people) x 145 (days in the year so far) = 1740 possible working days.

    and 35 days have been taken in holiday, therefore the percentage of holiday thus far = 0.02%

    So for each corresponding number in both of the crosstab queries, i need to take those two numbers, and the constant value entered (number of days thus far in the year), and do the maths above on those numbers. So the final report will show the numbers from the 2 crosstab queries, plus the percentage (separated by each category in the grid)

    So, if i had a query will all this information in , i would stand half a chance of producing a crosstab query from it. however, i have tried and tried, and can't seem to get it to produce the correct results.

    any help much appreciated.

    Mora

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query a Query. basic stuff (Access97)

    Go back and explain what the original crosstab queries are based on. What you're trying to do sounds unnecessarily complicated. And are you trying to produce a report or a query?

    It sounds like you're trying to use a parameter query, but which query do you have the parameter in? If it's in either of the original crosstabs, then it will affect their results. And I see no reason to use a crosstab at the highest level when you can filter a select query by the values in the crosstabs.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    London, Kent, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query a Query. basic stuff (Access97)

    One of the crosstab queries works out how many people are in each area, so i base this on another query,.

    ie: Query 1
    Store,---A Shift-----B Shift---- Days--- Nights
    Safeway--, 5-----6-----3-----2
    Tesco--------3-----2------1-----3

    So i have a crosstab query showing how many people are in each Store, and how many per shift..

    The second query is similar, but works on the number of days holiday they have had.
    Again this is separated by shift


    So i have two crosstab queries, with all the information in .. i can't seem to get all the information into 1 query.

    The original query that the crosstab queries are based on, have other further filters built into them.
    If i try to integrate the two queries, i end up not getting the right numbers.

    So from what i can see, i need to do one of the below ?

    a) start from scratch and have design a query that can extract everything in one go (And every time i try, i end up with the wrong results)

    [img]/forums/images/smilies/cool.gif[/img] havea query , question 2 queries and place the new information in a crosstab sourced from this.

    c) have a report that takes info from another report (Ive seen hints at this, but not sure if the other report has to be open at the same time? )

    Yours Confused..
    Mora

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

    Re: Query a Query. basic stuff (Access97)

    What do you want to show on the report, an example please.

    Do you wish the report to show:

    Store Shift #People HolidaysTaken %HolidaysTaken
    Safeway A 12 1740 0.02
    Safeway B 10 1450 0.03

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

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

    Re: Query a Query. basic stuff (Access97)

    It would make it a lot easier for Loungers to help you if you could post a (zipped) database with the tables and queries you have. The tables don't need to contain all records - just enough to demonstrate what you want - and you can modify or remove any sensitive data.

  6. #6
    New Lounger
    Join Date
    Jul 2002
    Location
    London, Kent, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query a Query. basic stuff (Access97)

    Hi again, patt has the idea just about spot on.

    HansV, its a small thing im working on at my place of work. who have strict rules on posting stuff. so i'm not allowed to. its a huuuge database anyways : )

    The two different crosstab queries extract data from two different tables.
    One crosstab query counts how many people are in an area. separated by department, and Shift.

    The other counts how many holidays have been taken. again, separated by Department, and shift.

    Each has a separate report.

    I need a separate report, that takes the information from these two queries, and places the information in a separate report. working out what percentage of holidays have been taken from the total number of days thus far in the year.

    I will try to take some screen shots of the actual queries, to see if it makes things clearer.

    Many thanks.

Posting Permissions

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