Getting "duplicate" records in query

[witeshadow]

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

[Rabbit]

That's because you haven't told SQL how to join the two tables. You need tell it that this field in this table needs to equal this other field in this other table.

[witeshadow]

Where do I do that? I tried to do that in the query, but then I would only get a few records instead of the 20+ I should be. Here is my Relationship report: https://www.dropbox.com/s/dlcfxaebk1hf5u7/Report1.pdf

[Rabbit]

Please attach the file to the thread itself. Our firewall blocks outside file services.

[zmbd]

From your posted SQL, there are no obvious relationship(s) between table [Job Bids] and table [Users]. So the query as written will take the cross product between the two table. The relationship is what you need to establish... and actually you may not even need the [users] table unless you need that name field for something.

What you need to do is open the Query in design mode.
I'm sure what you will see are just the two tables in the top portion, no lines between them, and your fields in the grid below.

Now take a look at the two tables... what field(s) are related between the two?

I would suspect something along the lines of:
[Job Bids].[Client ID] and [user].[id]
or
[Job Bids].[Client Name] and [Users].[First Name]

Left Click Drag on the field in [Job Bids] over to the field related field in [Users]. FYI: If you now click on the new line, and right click ON THE LINE, show properties, you will see the dialog box describing the relationship, for now you shouldn't need to adjust anything. Now run your query.... the results shouldn't be duplicated...

Next you really need to take a look at your query design, you have no filter on the user name

Where I think you intended to filter on the user name? If so, then you'll need to do some other tweaks to your query. Using parameters with queries and reports

Leading me to the next, your report... if you're only showing the records from the selected user, then you should not need a grouping on the user name.

You might want to take a look at: 169. Example Filtering on a Form. It's not exactly what you're doing with the report; however, it should lead you some different solutions and is along the same lines as the article I linked you to above.


>OK< It's Saturday here and I need to go help a close friend move a dryer (well two - old and new) into the basement.

Good luck.

[witeshadow]

I added a ZIP of the two files to this thread.

[zmbd]

witeshadow:
Sorry, most of us will not open a non-requested attachment from a relatively new member.
This has absolutely nothing to do with who you are... more like that most of us are at work and there are very strict requirements about downloads.
Very good people are often infected with malware and have no idea... in fact that happened to my Mom and she is fairly tech savvy!

Instead, you need to try an clearly state what is happening be exact with the error titles, numbers, and messages (really, be very literal with the errors) and how this differs from what your goal is.

[witeshadow]

I totally understand. I just am afraid that there are more details than I know to describe.

I've tried going into relationships and adding a relationship between Bidder in "Job Bids" to ID in Users, but I either get no results in my query, or the wrong results.

@zmbd

[zmbd]

You do not need to go into the table relationships

What you need to do is open the Query in design mode.
I'm sure what you will see are just the two tables in the top portion, no lines between them, and your fields in the grid below.

> Index of pages on Queries

> Microsoft Access for Beginners— Part III: Writing the Queries

This is for Office 2007; however, a good deal will transfer to 2010:
Access 2007 training courses