Invoice Numbering

[D-Lys]

Hello again,

I have another question where I would like Access to generate invoice
numbers when I run the query every month for billing. Each customer will have
a number predefined (4 or 5 digits), starting with the first customer then
all the rest incremented by one. Is this possible. I do not want to use the
randomly generated number by the system.

Thank you again.
D-Lys

 

[Fred]

I noticed that nobody answered.

Your post does not make it clear what you are trying to do.

Hope this feedback helps a little.

Fred

 

[D-Lys]

Thanks Fred.

Actually, I would like to auto generate number which are unique for each
customer's bill every month but those numbers need to be in sequence order,
with an increment of 1 from one bill to the next and need to be of 4 or 5
digits (i.e. 4500, 4501, 4502 and so on; or 45000, 45001, 45002, etc. for
every invoice generated for each customer, thus when the following month
billing is due, we will be able to start the numbering with a number greater
by 1 of the last number of the previous month. Is this possible?

Thanks for your precious help.

D-Lys.

 

[Michael Gramelspacher]

Actually, I would like to auto generate number which are unique for each
customer's bill every month but those numbers need to be in sequence order,
with an increment of 1 from one bill to the next and need to be of 4 or 5
digits (i.e. 4500, 4501, 4502 and so on; or 45000, 45001, 45002, etc. for
every invoice generated for each customer, thus when the following month
billing is due, we will be able to start the numbering with a number greater
by 1 of the last number of the previous month. Is this possible?

Wat is the reason for this? What happens after 4599, 4600? Is 4600 series already used for
another customer?

Maybe have a table with customer_id and sequence_num

Customer_id sequence_num
1 25000
2 26000

SELECT *, CustomersSequences.sequence_num FROM Customers INNER JOIN CustomesrSequences
ON Customers.customer_id = CustomersSequences.customer_id;

After the monthly bill, do an update to increases active customers' numbers by one.

I do not know how to prevent gaps if a bill is deleted. Maybe that is not an issue.

UPDATE Customers
INNER JOIN CustomersSequences
ON Customers.customer_id = CustomersSequences.customer_id
SET CustomersSequences.sequence_num = [sequence_num] + 1
WHERE Customers.is_active) = -1;

 

[D-Lys]

Thank you for your help Michael. I will try to apply your suggestion over the
weekend, hopefully will suit our needs.

Thank you much.
D-Lys

Actually, I would like to auto generate number which are unique for each
customer's bill every month but those numbers need to be in sequence order,
with an increment of 1 from one bill to the next and need to be of 4 or 5
digits (i.e. 4500, 4501, 4502 and so on; or 45000, 45001, 45002, etc. for
every invoice generated for each customer, thus when the following month
billing is due, we will be able to start the numbering with a number greater
by 1 of the last number of the previous month. Is this possible?

Wat is the reason for this? What happens after 4599, 4600? Is 4600 series already used for
another customer?

Maybe have a table with customer_id and sequence_num

Customer_id sequence_num
1 25000
2 26000

SELECT *, CustomersSequences.sequence_num FROM Customers INNER JOIN CustomesrSequences
ON Customers.customer_id = CustomersSequences.customer_id;

After the monthly bill, do an update to increases active customers' numbers by one.

I do not know how to prevent gaps if a bill is deleted. Maybe that is not an issue.

UPDATE Customers
INNER JOIN CustomersSequences
ON Customers.customer_id = CustomersSequences.customer_id
SET CustomersSequences.sequence_num = [sequence_num] + 1
WHERE Customers.is_active) = -1;

 

[Hal]

Hello again,

I have another question where I would like Access to generate invoice
numbers when I run the query every month for billing. Each customer will have
a number predefined (4 or 5 digits), starting with the first customer then
all the rest incremented by one. Is this possible. I do not want to use the
randomly generated number by the system.

Thank you again.
D-Lys

I have the same problem, and no one has yet given an answer that works!

I want to serially number a field in a Query or table file and we get no
answers!

Can it be done???

 

[Michael Gramelspacher]

I have the same problem, and no one has yet given an answer that works!

I want to serially number a field in a Query or table file and we get no
answers!

Can it be done???

Perhaps you did not get an answer, because you did not provide enough information.

Did you provide the query, which is supposed to have a serial number? What is the purpose of the
serial number? Serial numbers in queries are computed and involve ranking without ties based upon
some other query column or columns.

Serial numbers in forms are also computed columns based on adding one to the prior number for each
new record. When records are deleted, gaps are created.

Did you google Access groups for previous discussions on serial numbers?

 

[Hal]

Perhaps you did not get an answer, because you did not provide enough information.

Did you provide the query, which is supposed to have a serial number? What is the purpose of the
serial number? Serial numbers in queries are computed and involve ranking without ties based upon
some other query column or columns.

Serial numbers in forms are also computed columns based on adding one to the prior number for each
new record. When records are deleted, gaps are created.

Did you google Access groups for previous discussions on serial numbers?

Michael,
Can I send a bipmap that reflect the 2 tables and 1 query I am working with.
If so how do I send the bipmap?

You need not question the reasoning behind why we are doing this, just help
us git-r-done.

Hal

 

[Michael Gramelspacher]

Michael,
Can I send a bipmap that reflect the 2 tables and 1 query I am working with.
If so how do I send the bipmap?

Normal procedure is to post the URL where the image can be viewed, and then anyone here can view it
and offer a suggestion.

You need not question the reasoning behind why we are doing this, just help
us git-r-done.

And likewise, no one need help you get it done.

 

[Larry Linson]

And likewise, no one need help you get it done.

OK, M. G., I guess you are not, after all, Steve's sock-puppet -- much too
practical a response for Steve.

Hal, keep that number in a separate Field, and you can use the DMAX domain
aggregate function to calculate and store the next one, which will be
returned in the next DMAX. Concatenate the prefix and the sequential number
when you display or print the customer number, or, if you don't worry about
redundant storage and the possibility of "what do I do when the customer
number" and the "number which is a part" not matching at some point,
concatenate and store in your table.

There's good Help on DMAX. If you'd rather, you could use a totals query to
accomplish the same result, but any performance difference would probably
not be significant under the circumstances.

Larry Linson
Micrrosoft Office Access MVP