Update Query for matching index value

[shanef1981]

Hey guys,

I'm a very experienced Access programmer struggling with a query issue - see if I can explain it well enough.

I have to send an electronic bill to one of my clients which I have built in Access as an exported query to an XLS.

All is great until they provide more than one cost center for an invoice, when I then get duplicate lines.

Rather than concatenate the multiple values on one line, they want me to set the "InvoiceTotalLines" value to the amount of different lines in the files (i.e: the number of differing cost centres), split the invoice value across these lines and then have an incremented "InvoiceLine" value for each line.

Not sure if I'm explaining this very well, but they need this to be able to upload it into their SAP system (SAP is CRAP).


Cheers...

 

[shanef1981]

just had a brainwave - what I need is some method for it to count how many matching invoice numbers appear in the query - somehow...

 

[shanef1981]

i've illustrated it below - think this should nail it...


What it currently looks like
Invoice InvoiceDate InvoiceLine InvoiceTotalLines Amount SAPProject SAPActivity
1101 21/12/2007 1 1 500.21 12 2001
1102 25/12/2007 1 1 4500.2 13 8002
1102 25/12/2007 1 1 4500.2 16 9003
1102 25/12/2007 1 1 4500.2 16 4005
1102 25/12/2007 1 1 4500.2 17 6001
1105 01/01/2008 1 1 250.23 16 9003

What it should look like

Invoice InvoiceDate InvoiceLine InvoiceTotalLines Amount SAPProject SAPActivity
1101 21/12/2007 1 1 500.21 12 2001
1102 25/12/2007 1 4 1125.05 13 8002
1102 25/12/2007 2 4 1125.05 16 9003
1102 25/12/2007 3 4 1125.05 16 4005
1102 25/12/2007 4 4 1125.05 17 6001
1105 01/01/2008 1 1 250.23 16 9003

 

[HarleyQuinn]

Would you be able to post some sample data, your current query and an example of your desired output?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Beat me to it

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

This might be of some help faq701-5268

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.