Row number on Report issue...

[daze]

Please can anyone help...

I want to have line item numbers on report look like this:

Number....Order Number
1............9533-1425-20
2............5866-3411-14
3............2332-2355-14
3............2332-2355-14 ->repeating item
4............4399-5208-12
5............7392-2305-62
5............7392-2305-62 ->repeating item
6............2332-2355-14

Each new row of record needs to have their own line item number starting from 1 with criteria if order number is the same then carryover line item number on that record.

Any ideas?

P.S. I know how to get line item starting from 1 to last by assigning unbound control field with entry "=1".

 

[plog]

First 'Number' is a poor choice for a field name because it is a reserved word (http://support.microsoft.com/kb/286335). Second, 'Order Number' is a poor choice for a field name because it has a special character in it (space) which makes writing queries and code a little more difficult. I suggest you fix both of those.

For your issue, you would do this in a query by adding what is essentially a running total field. If the data you posted is a table, you would build a query to generate this running total field, if it already is a query you would just add that field.

Search this forum for 'running total' / 'running count' queries, there should be lots of posts. In general, you would use a Dcount(http://www.techonthenet.com/access/functions/domain/dcount.php) to look into the source data to count the number of records that are less than the current record's value.

 

[daze]

Thanks for answer.
I'll give it a try.

BTW - NUMBER and ORDER NUMBER are purely an exapmles of column names.
And yes I do use CapitalLetters without space(s) as column names in table.

 

[daze]

So, I still didn't get this to work.
I'm mot sure how to make it work.

I tried with queries, but no luck.


Can someone give me some more guidelines or examples?

 

[plog]

Can you post the name of the table that data is coming out of? Also, what are the exact field names?

 

[daze]

OK, here we go:

Table name: ReceiptsData
I need to check if this table's field InvoiceNumber (text field) repeats on report.
Count of record should start from 1, then 2 and so on into unbound field called LineItemNumber.
If InvoiceNumber repeats itself then carryover whatever number was the last to repeating row in LineItemNumber.

 

[plog]

I found a discrepanacy in the data you posted and your description: 2332-2355-14 is given both number 3 and number 6. Is that just a typo or can that InvoiceNumber actually have 2 different LineItemNumbers? If so, how?

 

[daze]

You are correct. As I was retyping I made mistake.
last row number 6 is this: 2332-9000-14.

Thank you for noticing this error.

 

[plog]

Ok, good. To accomplish this you will first you will need a subquery to get all unique Invoice Numbers:

SELECT InvoiceNumber
FROM ReceiptsData
GROUP BY InvoiceNumber;

Save that query as 'sub_InvoiceLineItems'. Then you can build your ordering query based on ReceiptsData:

SELECT ReceiptsData.*, DCount("[InvoiceNumber]","sub_InvoiceLineItems","[InvoiceNumber]<='" & [InvoiceNumber] & "'") AS LineItemNumber
FROM ReceiptsData
ORDER BY DCount("[InvoiceNumber]","sub_InvoiceLineItems","[InvoiceNumber]<='" & [InvoiceNumber] & "'");

That query will produce the ordering numbers you want.

 

[daze]

Super done.
Excellent.

But, I'd need to have line item sorted by date...
Yes, I have InvoiceDate field too.

I tried to get it working, but no luck.
Any input there?

I noticed you were sorting them by InvoiceNumber from smaller to bigger.
Normally it would be good, but in my case I actually need to have them sorted by date.

 

[plog]

What date? And by that, you sometimes have 3 InvoiceNumbers, could each of those 3 have their own date? If so, which one do you want to assign to that invoice?

What you need to do is to reconfigure the sub query to assign the date to every InvoiceNumber. Then you need to change the Dcount in the main query so that its criteria uses that date field instead of the Invoice Number to determine order.

 

[daze]

No, InvoiceNumber are closely tied with the SAME date.
If you have more than one (not more than two actually) InvoiceNumber's they will have the same date.

I was driven by your logic but was unable to produce correct sorting...

 

[plog]

What did you do exactly? What does your Dcount look like?

 

[daze]

I've replaced InvoiceNumber with InvoiceDate:
DCount("[InvoiceNumber]","sub_InvoiceLineItems","[InvoiceDate]<='" & [InvoiceDate] & "'") AS LineItemNumber

It gives me error.

 

[plog]

You still need the InvoiceNumber in there and I believe when you compare dates you need to escape it with pound signs (#) instead of single quotes.

 

[daze]

I actually knew that and tried it but didn't put # sign in double quotes.
I'll give it a try now.

 

[daze]

I get syntax error with this code:
LineItemNumber: DCount("[InvoiceNumber]";"sub_InvoiceLineItems";"[InvoiceDate]<= #" & [InvoiceDate] & "#")

I cannot get it to work! grrrrrr

 

[plog]

Your commas got changed to semi colons.

 

[daze]

Actually when I turn SQL view it's commas...
The error is this:
Syntax error in date in query expression '[InvoiceDate]<=#7.3.2041.'. -> european date format.

 

[daze]

This is the SQL syntax I have:

SELECT ReceiptsData.*, DCount("[InvoiceNumber]","sub_InvoiceLineItems","[InvoiceDate]<=#" & [InvoiceDate] & "#") AS LineItemNumber
FROM ReceiptsData
ORDER BY DCount("[InvoiceNumber]","sub_InvoiceLineItems","[InvoiceDate]<=#" & [InvoiceDate] & "#");

This gives error in previous post.