Query first record of each unique foreign key

[nd0911]

Hello,



I want to query a table.

its a payments table that the Id field is PaymentId and also have a foreign key CustomerId and more fields.

the thing is that I want to see in the results every customerId only one time (even if he is there alot of times), actually show me the first record of each unique customerId is in the table.

thank you.

[ranman256]

make 2 queries....

Q1:to get the eariest ID using MIN:
select custID, Min(PayID) from tPayments

then in Q2, get all the data
Q2:
select tCustomers.* from Q1, tCustomers where tCustomers.custID = Q1.custID

[ArviLaanemets]

How you determine the 1st occurrence of key? Access tables (and any relational databases tables) don't have any internal row position. You must have some parameter to decide, which entry is earlier and which later one. P.e. when PaymentID is autonumeric (and those autonumeric PaymentID's were never recreated anew), then the condition can be that you search smallest PaymentID value for every CustomerID. Or when you have PaymentDate field in table, and there are no duplicate payments on same day, then the condition is smallest PaymentDate for every CustomerID.

[nd0911]

Thank you very much but I was wrong.

I have one more important field "PayDate", instead of get the first record of each unique customerId I need the "Min" of "PayDate" field of each unique customerId.

[nd0911]

someone ??

[Micron]

Can you not alter what was posted in #2 to suit your new requirements? If not, maybe if you posted some data and revealed the exact names of your tables/fields. This is probably also possible using a subquery, but without knowing those names, it's too difficult to post meaningful/accurate sql statements. Or you could look at here at TOP N Records per Group and use the Min of your date field instead. In that case, you wouldn't use IN as shown in the example, but more likely =.

http://allenbrowne.com/subquery-01.html

[nd0911]

Ok, here is the table filelds and my needs:

table name:tbl_Payments

Fields: PaymentId (Master Key), CustomerId, ProductSaleId, Cost, Price, PayDate, Notes

My needs:
Query that shows every customerId only one time with the earliest date in the PayDate field, and if there is several of the same PayDate that is the earliest, show only the one with the smallest PaymentId, the main rule that in the end every customerId will have one record.

I hope I was more clear now.

Thank you !

[Micron]

Did you forget to include some data in your last post?

[nd0911]

No I didn't but I can.

PaymentId , CustomerId, ProductSaleId, Cost, Price, PayDate, Notes
1 , 1 , 1 , 100, 300 , 1/1/18, bla bla
2 , 1 , 2 , 110, 320 , 1/1/18,
3 , 2 , 3 , 80 , 200 , 3/1/18,
4 , 1 , 1 , 100, 300 , 1/2/18,
5 , 2 , 3 , 110, 320 , 1/2/18, bla bla 2

[Micron]

Well, it seems that things have changed. From post 4:

instead of get the first record of each unique customerId I need the "Min" of "PayDate"

Now it's

the earliest date in the PayDate field, and if there is several of the same PayDate that is the earliest, show only the one with the smallest PaymentId,

As far as I know, a subquery can "filter" a group of records based on one field only and you seem to now be saying 'it's field A unless condition1, then it's field B. I don't think that is possible, because what a subquery returns is kind of "linked" to the outer query on the same field, thus doesn't allow for flipping between field A or field B. At least I wouldn't know how to do that.

You never commented on your ability to adapt what was given in post 2. Seems like you should try that.