Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: I'm getting "duplicate" records in my query

    Basically (and I'm sure i'm not doing this the "right way") I have a user field that the person who created the record is supposed to select their name from. It is linked to a Users table. I have a query that pulls the records created in the last 7 days, I then have a report that lists those records, sorted and grouped by user and then day.

    Here is my SQL for my Query:

    SELECT [Job Bids].[Job Site Name], [Job Bids].[Date Applied], [Job Bids].[Date Posted], [Job Bids].[Position Name], [Job Bids].[Client Name], [Job Bids].Country, [Job Bids].[Bid Type], [Job Bids].[Bid (Gross)], [Job Bids].[Bid (Net)], [Job Bids].URL, [Job Bids].[Post ID], [Job Bids].Email, [Job Bids].Skype, [Job Bids].Invited, [Job Bids].[Client ID], [Job Bids].[Bid (Net)], [Job Bids].[Bid (Gross)], [Job Bids].[Job Description], [Job Bids].[Date Applied], DateDiff("w",[Date Posted],Now()) AS Elapsed, Users.[First Name], [Job Bids].Bidder
    FROM [Job Bids], Users
    WHERE (((DateDiff("w",[Date Posted],Now()))<7));

    I can post the database, but I would need to create a few fake records instead of the real data. What happens, is that each record is shown, BUT it is duplicated with one for each user (so i'm being added to the 2nd record for example, even though I should not be). The Report is just showing two records under the actual user.

    Thanks,
    Chris

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the duplicate data is ghost data, ie its seen in a query but not in the underlying tbaels then its almost certain that you have a missing or incorrect join.

    my guess is it is because you are referring to users without any form of join
    so either drop the users[first name]
    OR
    define a join that associates job bids with users
    you should be using the modern join syntax, but at a pinch you can use the old style 'theta' / 'where' syntax

    eg
    Code:
    join users on users.userid = [job bids].userid
    ..assuming that the comparable column is called userid in both tables. it need not be and it does depend on what you have called the relevant columns in the relevant tables
    or
    Code:
    WHERE (((DateDiff("w",[Date Posted],Now()))<7))
     and users.userid = [job bids].userid;
    to make life easier for yourself in future |I'd strongly recommend that you drop spaces in column/table names. if yiou want human centred labels then define them as part of the column definition (IIRC its caption)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    4

    Here are sanitized copies of my database.

    I've Attached a zip of the Access files.
    Attached Files Attached Files
    Last edited by witeshadow; 03-19-13 at 13:19.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how is table job bids related to table user?
    that is where you should design your join
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    4

    Job Bids & Users

    Job Bids should be linked to a user, ie I should be able to select someone from the Users table as the author of a record in "Job Bids".

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fine
    so what column in table Job Bids maps to what column in Users?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2013
    Posts
    4

    relationship

    I went into relationships and linked Bidders in "Job Bids" to ID in Users.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you have defined your relationship in the relationships window
    either
    start the query from scratch again and this time the query designer whould pull the join in automatically
    or
    define a join inside the query
    Code:
    join Users on Users.ID = [Job Bids].Bidders
    Depending on the level of knowledge then the first option may be smarter as Access does seem to use a slightly odd variant of the Join syntax
    Last edited by healdem; 03-20-13 at 05:57.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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