Results 1 to 10 of 10

Thread: Query....Help

  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Question Unanswered: Query....Help

    I need to write query based on three tables that i have.

    Table 1 : Cust_ID
    Table2 : Order, Cust_ID
    Table3 : Payment, Cust_ID

    Basically, i need to write query that will give me result as : Total # of order and Total# of payment for giving date range.

    Total# of Payment is not total of all amount, it should be how many payment received by one customer.

    Thanks in advance
    Skharva

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Pull all three tables into a query. Make sure all three tables are linked by CustID. Pull CustID from Table 1, Order from Table 2, and Payment from Table 3. Make your query a totals query (sigma button), group on CustID and Count Order and Payment. Use the appropriate date fields for your criteria and use Where in the totals row.

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Exclamation

    I get the right results when i join two table but as soon as i add 3rd table in query i do not get right result...I'm posting my query

    SELECT tblMothers.MotherID, Count(tblScreening.ScreeningDate) AS CountOfScreeningDate, Count(tblInfants.IPTID) AS CountOfIPTID
    FROM (tblMothers INNER JOIN tblScreening ON tblMothers.MotherID = tblScreening.MotherID) INNER JOIN tblInfants ON tblMothers.MotherID = tblInfants.MotherID
    GROUP BY tblMothers.MotherID;
    Skharva

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Nested Query

    I have come across this in some of the queries I have built. What you may need to do is create a nested query.

    Create query 1. pull in tables 1 and 2 join on Customer ID and group and sum the values.

    You will now have a table with the same number of rows as the first (assuming all of the customers had orders)

    then create query 2, pull in the query you just created, then add table three
    group and sum and viola numbers should be good to go.

    Why this happens is that if there different number of values in the secondary tables. For instance
    Customer 1 has two orders and for each of those orders they had two payments. So this would result in four rows in your queury
    custID orderID PaymentID
    1 O1 P1
    1 O1 P2
    1 O1 P1
    1 O2 P2

    When the computer groups on customer id and counts orders and payments your order count is doubled.

  5. #5
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Thanks...that wrk fine
    Last edited by Skharva; 04-28-04 at 15:22.
    Skharva

  6. #6
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    How can i displya time frame in reports based on the query ?

    This is how i would like my reports to be:

    TimFrame #Screened #Enrolled
    _______________________________
    01/Jan/06 10 20
    02/Jan/06 5 5


    TimeFrame is not the field in table. I know how to get #Screend and #Enrolled.
    Skharva

  7. #7
    Join Date
    Dec 2003
    Posts
    268
    Does the time frame have some type of key associated with the customerID?

    Just add it to your second query and pull in the information.

  8. #8
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Unhappy

    No. time frame is not associated with any filed from table.
    Skharva

  9. #9
    Join Date
    Dec 2003
    Posts
    268
    Then where are you going to pull this from?

  10. #10
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Exclamation

    Well for my other reports i have created the form where user will enter StartDate and EndDate and then the report will preview data for that time range. For this one report i would like to see the time frame for example:

    TimeFrame | Order
    ---------------------------------
    28-Apr-04 | 100
    29-Apr-04 | 200


    Again, "TimeFrame" is not anyfield from table. Is it possible to do this ?
    Skharva

Posting Permissions

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