How to add an incrementing number to a report 2

[lridlen]

Hi Remou,
sorry about the continued thread. This is the first time I have used this and not sure how to make a new thread and us both still follow it....

anyway, assigning the relationship to the commission table would give me a new invoice number for each commission entry and that's not what I want. I want a new invoice number per dealer - but the dealer's rarely change, so I can't make the relationship between dealer and tblinvoice.

the report is based on a query which includes both tblcommission and tbldealer, but is sorted by dealer with a page break for each new dealer entry. The commissions per dealer are then listed on their respective pages.

This is why I thought I needed an auto generated number on the report.

Louise

 

[Remou]

Just to keep anyone else informed, this thread started in
How to add an incrementing number to a report
thread703-1144860
That is not quite what I visualised, what I thought was:
[tt]tblDealers
DealerID
1
2

tblInvoice
InvoiceID DealerID
1 1
2 2
3 1
4 2

tblCommission
CommissionID InvoiceID
1 1
2 1
3 1
4 4
5 4[/tt]

In what way have I missed your point?

 

[lridlen]

Hi Remou,

Thanks for being so patient with me!!!

I understand what you're saying. Currently my report is based on the commission table, in your example above I should add an Invoice table and the report should build off this invoice table?

If this is the case, how do I make a new invoice number appear at each change of dealer (each new page) in the report?

lridlen

 

[ZmrAbdulla]

place a textbox in the detail section setting the

controlsource =1 (including the equal sign)
Running Sum= OverGroup


________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[Remou]

A report is generally a picture of what is going on in your database; it does not control events. What I imagine is that say, once a month, you send out invoices to various dealers for commissions that have not been invoiced so far. If this is the case, your code would have to:

* Select from commissions where invoice number is null, ordered by dealer number;
* Append a record to invoices for each dealer found in commissions and add a date;
* Update commissions to show this invoice number;
* Print a report for date, with the possible option of selecting a different date.

Your report would consist of a query joining the invoice table to the dealer table and a subreport to show each commission line being invoiced.

Does that sound right?

 

[BobRodes]

Perhaps this background will be helpful: as a general rule, reports are built on queries rather than directly from tables. This is because the best way to present data isn't usually the best way to store it. Queries select and prepare the desired data for the report from the underlying tables of data in storage.

Bob

 

[lridlen]

Thanks guys,
This is all useful information. I understand what you are saying Remou and you're right. I'm ok with step 1 and 4 of your suggestion, but not sure how to go about steps 2 and 3??
lridlen

 

[Remou]

I have done a rough mock-up using queries only. However, you may wish to use code. Hopefully the notes below will help you decide.

You should note that:
* These queries are only tested very lightly;
* I have assumed that Invoice Number is an AutoNumber, which is not always a good idea;
* I have used a small form called frmCreateInvoices with a textbox, txtRunDate, for the date;
* The whole thing is wide open to mistakes.

1. Select from commissions where invoice number is null:
[tt]SELECT DISTINCT tblCommissions.DealerID
FROM tblCommissions
WHERE (((tblCommissions.InvoiceID) Is Null));[/tt]
This will select each dealer once only.

2. Append a record to invoices for each dealer:
[tt]INSERT INTO tblInvoices ( DealerID, InvoiceDate )
SELECT QryInvoiceID_Is_Null.DealerID, [Forms]![frmCreateInvoices].[txtRunDate] AS RunDate
FROM QryInvoiceID_Is_Null;[/tt]

3. Update commissions to show this invoice number:
[tt]UPDATE tblCommissions INNER JOIN tblInvoices ON tblCommissions.DealerID = tblInvoices.DealerID SET tblCommissions.InvoiceID = [tblinvoices].[invoiceid]
WHERE (((tblCommissions.InvoiceID) Is Null) AND ((tblInvoices.InvoiceDate)=[Forms]![frmCreateInvoices].[txtRunDate]));[/tt]