i need to number duplicate references as 1,2,3,4 so i can use them in a query.

[exlonghair]

Hi All,

only started poking around with access a couple of weeks ago as I was unable to find a way to do something in excel. I am trying to sort out a payment reconciliation sheet where I can show all payments received against a contract number, only problem is the payments are only identified by that contract number and there can be up to 4 separate payments per contract which arrive at different times, I need everything to show on a single line in the query results so they can be easily used by finance to see what for, when and how much we have received for each contract. I am getting a lot of duplicated results at the moment as I can only figure out how to separate up to 3 of the payment types using queries on my raw data, if I could label each occurrence of a duplicate as 1,2,3,4 etc I could separate them out using queries and place them in order on the main query matching them to our jobs.

I am not familiar with SQL or Access at all and most of the answers I have seen for this make no sense to me.:banghead:

basically I have 72000 payments coming in against 57000 jobs (at the moment) but I don't want any duplicates in the query that matches payments to jobs as this duplicates the entire line giving a misleading result when the totals are counted.

each payment has up to 6 separate amount on it and the only identifier is the contract ref they are paid against.

I need to be able to lay out a query as follows:

job - payment 1 - payment 2 - payment 3 - payment 4 - total received

where each payment can come at any time for different amounts with just the contract ref identifying which job it is for. laying out the query is not a problem, sorting out of the payments has not been too much of a problem its just when we get a totally random payment that doesn't fit into the criteria I have, obviously it duplicates everything on a separate line screwing up the totals. what I want to see is a query that produces all 57000 jobs with upto 4 payments against each, if I could label each time a payment is received for a particular contract ref as 1 for the first time 2 for the second etc it would make my life so much easier.

this is an ongoing project where payments and jobs will be added on a monthly basis, numbering in the thousands for each, I am so close to getting it right its driving me mad that I am stuck on something that sounds so simple in plain English but is proving extremely difficult to do inside a computer.

any assistance would be gratefully received.

 

[pbaldy]

Is this the type of thing you're after?

http://allenbrowne.com/func-concat.html

 

[exlonghair]

id like to say that was useful, but errr I have no idea.

I need to be able to get a formula or something that will look at the account number and say 1 if it is the first and possibly only payment, and 2 if there is a second occurrence of the account number, 3 for the third etc.

the rest I can work out for myself I think, its is literally just adding a number to each line saying which occurrence of that account number it is.

thanks for the quick response but that may as well have been in Chinese ;-)