Northwind example database

[Paul]

Dears
I want to use the northwind database as example for my own project however
there is one problem I can't seem to solve.
this is the situation :
1 have a customer database of 2000 customers for which I have to create
monthly several commission notes 1 commission note is generated by an
external application and all the rest is created manually.
this commission note has a number which is unique because the first digits
contains the customer in question and the last three digits is a
autonumbering sequence. example: if customer a gets today a commission note
with number 200125 the next one will be 200126.
the same system has to be implemented for the other commission notes that we
create manually each month.
is there a way to do this in such a way that each customer has his own
autonumbering?
can I also integrate this is one table?

thanks for your coop and feedback

Paul

 

[Kernow Girl]

Hi Paul - your commission table should do this automatically. Set up a Table
for the commissions, give it an Autonumber field. Link it with the Customer
Table using the Customer ID which is unique and then print both of them on
your commission note.
The Commission Table will then contain the unique Autonumber of the
Commission Table and the Customer ID. Yours - Dika

 

[Paul]

Dika
in a way this is what I need however I have to have a range for customer 1
and a seperate range for customer 2 and I don't know how to do this

 

[Kernow Girl]

I see what you mean. In that case you won't be able to use the autonumbering.
it just keeps chuntering along to the next record number. It looks like you
would have to hold a counter in your customer record and simply add 1 each
time you create a new commission record. What happens to the commission note
printed by the external system, do you need to 'add' it back into the access
DB? Dika

 

[Tom Wickerath]

Hi Paul,

What you are describing is typically known as an "Intelligent Key" - and
that's not a compliment. Try doing a Google Advanced Search for the exact
phrase: Intelligent Key. Here is an example that returns 225 hits, for the
groups: microsoft.public.access.*

http://groups.google.com/groups?q="...81&as_maxd=22&as_maxm=3&as_maxy=2006&safe=off

If you really want to proceed with this, then you should store the customer
number (200 in your example) and the sequence numbers (125, 126, etc.) in
separate fields. You can concatenate them at run-time, so that they are
displayed on a form as 200125, 200126 (or 200-125, 200-126). You will likely
need to use the DMAX + 1 method, where you include a criteria on the customer
number field (200) to find the maximum sequence number for that customer, and
then add one to it.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi Paul,

What you are describing is typically known as an "Intelligent Key" - and
that's not a compliment. Try doing a Google Advanced Search for the exact
phrase: Intelligent Key. Here is an example that returns 225 hits, for the
groups: microsoft.public.access.*

http://groups.google.com/groups?q="...81&as_maxd=22&as_maxm=3&as_maxy=2006&safe=off

If you really want to proceed with this, then you should store the customer
number (200 in your example) and the sequence numbers (125, 126, etc.) in
separate fields. You can concatenate them at run-time, so that they are
displayed on a form as 200125, 200126 (or 200-125, 200-126). You will likely
need to use the DMAX + 1 method, where you include a criteria on the customer
number field (200) to find the maximum sequence number for that customer, and
then add one to it.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Sarapp]

Dika
in a way this is what I need however I have to have a range for customer 1
and a seperate range for customer 2 and I don't know how to do this