Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2017
    Posts
    2

    Unanswered: Query returns duplicate values

    Hello forum, I am new to the forum and quite new to the MS Access world.

    I am trying to get a query to work but when I run it for one of the fields (tblGGR.[201401]) it is showing duplicated results. This is the query:

    Code:
    SELECT tblGGR.Book, tblGGR.[201401], [tblGGR].[201401]/Sum(tblGGR.[201401]) AS GGRPerc, tblTurnover.[201401], [tblTurnover].[201401]/Sum(tblTurnover.[201401]) AS TurnoverPerc
    FROM tblTurnover, tblGGR
    GROUP BY tblGGR.Book, tblGGR.[201401], tblTurnover.[201401]
    ORDER BY tblTurnover.[201401] DESC;
    For all the Books (13 in total) I am getting 13 different results from tblGGR.[201401] instead I want that for each Book I get one result from tblGGR and one from tblTurnover.

    If I am not clear apologies, will try to clarify

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi

    Not sure what values you are dealing with, but if tblGGR.[201401] and tblTurnover.[201401] have different values entered for each book then you will get a record for each book value. Also, this is a Cartesian JOIN, ie there is no relationship specified between the two table (assuming there is one). This in itself will give rise to multiple records. I could be wrong, but I will be surprised if that is your intention.

    HTH


    MTB

  3. #3
    Join Date
    Jun 2017
    Posts
    2
    thanks for your answer.

    Yes Turnover and GGR are 2 different values but each Book appears in both tables only once. The result I get looks like the following:

    Books Turnover GGR
    Book1 3.00 0.50
    Book1 3.00 0.13
    Book1 3.00 0.01
    .
    .
    .

    So basically even if Book1 is appearing only once in tblGGR and once in tblTurnover I have multiple entries for each book. Specifically I have 13 entries (equal to the the total number of Books) where only GGR changes.

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    188
    You should JOIN both tables together on common value Fields in both tables.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  5. #5
    Join Date
    Apr 2017
    Posts
    14
    22764636, do you know what a JOIN is? I suggest you read the literature on JOINs, especially LEFT JOINs. This will

    1 - define the LEFT JOIN
    2 - help you figure out which table should be on the LEFT, and
    3 - spell out how to actually code it within the SQL Select statement.

    No one is hiding anything. You need to familiarize yourself with the HELP literature so you won't have to post elementary questions on the forum. On my first Access job, I spent more time reading the help files than actually coding.

Posting Permissions

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