Queries, using record entries only once?

[no1beyondfan]

Hi,

I am a beginner in access and really struggling.

Summary:
2 Tables:

Table1: Quotes, Unique Key is Illustration ID
Table2: Sales, primary key is Sale ID

Multiple quotes will happen before a sale.

I need a query that gets the Customer ID/Cost in Sales and match that with the Quote Customer ID/Costs.

I have about 1800 quotes, 1200 sales... but 4000 returns on a query??

I just want to match the exact customer ID and cost from sales to illustrations, but once that record is matched , then move onto the next one. this because some sales and quotes are the same.

E.g.

Quote
ID1 Bob £10
ID2 Bob £10
ID3 Bob £11
ID4 Bob £10
ID5 Bob £10
ID6 Bob £12
ID7 Bob £15
ID8 Bob £19

Sales
ID50 Bob £10
ID51 bob £11
ID52 Bob £10

Someone give me a hand?

[no1beyondfan]

So I need the query to do this :

Sales Quotes
ID50 Bob £10 = ID1 Bob £10
ID51 bob £11 = ID3 Bob £11
ID52 Bob £10 = ID4 Bob £10

[Rixxe]

From your example, i cannot tell what you are joining on:

If: ID50 Bob £10 = ID1 Bob £10

What are you joining the tables with?

If: ID1 Bob £10 = ID1 Bob £10

I Could understand you would use the ID

Either way, If you want to join the tables together, you need to look at JOINS (Inner, left, right, etc)(google it) and for Criteria, WHERE clause.

Let us know how far you get (i.e. show us the code)

[no1beyondfan]

Hi,

Sorry, I really don't undertstand the JOINS (Inner, left, right, etc). I will google it.

i meant:

Quote Table
ID1 Bob £10
ID2 Bob £10
ID3 Bob £11
ID4 Bob £10
ID5 Bob £10
ID6 Bob £12
ID7 Bob £15
ID8 Bob £19

Sales Table
ID50 Bob £10
ID51 bob £11
ID52 Bob £10

In a query?
Sale ID50 Bob £10 is matched with "bob" as a criterea and then "£10" to get any entry e.g. ID1 Bob £10. but once ID 1 is used it wont be used again when ID52 with the same criterea is chosen brings up the next identical entry?

I will google Join... where would I enter Join stuff?

[Rixxe]

Oh right i get what you are trying to do:

Forget what i said before if you are not typing the code in yourself.

In the design view; For the Criteria section, set the fields that you want to find the name as : 'Bob'
Then set the field that you want to find the price as: Like '£10' or '£11'

That should work, join the tables on those fields using the design view, that should be all it takes.

[no1beyondfan]

Thanks,

The problem is The Quote table has bob, james, and about 1800 entries.

The sales will also have bob , james etc about 1200 entry, I want to run a query that matches them , but only once....

is it possible?

thanks for the awesome speedy replies btw!

[Rixxe]

So you want to have 1200 Total? and 600 records left over from 'The Quote' table?

[no1beyondfan]

In theory yes,...

1200 sales may not all come from 1800 quotes.

But say 1000 sales came from 1000 quotes.

The other 200 sales may not be matched.

I just want to match the customer name and cost.

the sale of john at £10 can be matched to any john quote for £10 or vice versa Im not bothered, its the fact that I don't want the same quote used twice... I want once the quote has been used... it neglected for the next sale?

[Rixxe]

My Brain is a little fried today, but as far as i know, if you are going to exclude them for the next time you do a query or the next time you match the tables to each other, it's going to be alot more complex.

However, don't you get all the records you want when you match the Name - Name
and Price - Price fields between the two tables?

[no1beyondfan]

Yes it does, but the sale 1 is matched with quote A

Then Sale 2 is also matched with Quote A!

I want it to say Quote B, because Quote A has been used and these duplicates are making it wrong???

[no1beyondfan]

might try a macro?

[Rixxe]

If you have duplicates then i'd suggest looking into GROUP BY, its avaliable with the design view.
Other wise i'd suggest uploading a sample of you DB, zipped to this site so that others can have a look and help you solve the issue.