Solved Auto Number in Query that pulls from 2 tables (1 Viewer)

[nicko14]

Hi All,

Im starting a candle company and have 2 tables:

1 table tblCandleSize

Fields:
CandleSize (The full spelling of said size, Small, Medium, etc..)
CandleSizeSku (S for Small, M for Medium, etc..)

1 table tblCandleScent

Fields:
CandleScent (The full spelling of said scent)
CandleScentSku (An abbreviation of said scent)

in my Query qryProductSkus I have it so every combination has a unique combined SKU

The field I created is:

IndividualProductSku: [CandleSizeSku] & "-" & [CandleScentSku] & "-" & "100"

So a small watermelon candle would have a sku of: S-WM-100, example below:

Now what I am trying to do is create another field to add an auto generated number to assign to each Sku. I have watched some videos and read some posts, but have come up empty.

Is this possible and if so, could someone please help.


Very greatfully,

Nick

 

[The_Doc_Man]

Is this possible and if so, could someone please help.

Yes, if you are generating numbers, a lot of things are possible. The ONLY help you can get right now though is advice on how to ask the question in a way that it can be answered.

When you auto-generate this number you are seeking, what pattern - if any - is required? Will the number repeat across certain product types? Is it date-related? Does it have range constraints? Without this information, my answer to your second question is "NO, for the moment."

 

[plog]

Now what I am trying to do is create another field to add an auto generated number to assign to each Sku

Not in a query you cannot. But more importantly, why?

What will this number do for you?

 

[nicko14]

Yes, if you are generating numbers, a lot of things are possible. The ONLY help you can get right now though is advice on how to ask the question in a way that it can be answered.

When you auto-generate this number you are seeking, what pattern - if any - is required? Will the number repeat across certain product types? Is it date-related? Does it have range constraints? Without this information, my answer to your second question is "NO, for the moment."

No just a unique number starting at 1, then 2, etc.

The number will not repeat, it will be unique.
Not date related
No constraints

 

[nicko14]

Not in a query you cannot. But more importantly, why?

What will this number do for you?

Save me manual entry. I need it to generate UPCs. Every product requires a unique UPC and this number would help me get that for every product I make. I can enter them manually on an excel sheet after an export, but I just wanted to know if it was possible and to keep everything in access.

 

[plog]

A query can create unique numbers for each record you have, but those numbers are not guaranteed to remain with the same record each time you run the query. If you add a size or a or a scent to your database the next time you run the query the numbers for the entire set would be off from the last time you run it. So a query is not the answer.

I think you need to make a table of all scent/size combinations and add an autonumber to it. Here's the steps:

1. Make a query using this SQL:

SELECT CandleSizeSku, CandleScentSku FROM tblCandleSize, tblCandleScent

Paste it into a new query object and call it something like "qryScentsAndSizes".

2. Make another query based on qryScentsAndSizes. Bring down both fields into the query then change it to a MakeTable and make a table called "tblScentsAndSizes". Run it and you can now discard this MakeTable query.

3. In Design View of tblScentsAndSizes add a field called "ssID", make it an autonumber and make it the primary key. Save the table, then open it to see the numbers for each record.

That creates the unique numbers you need for all the scents and sizes you have. Lastly you should create a query to find and add records to tblScentsAndSizes for when you add a scent or size. This is that query:

INSERT INTO tblScentsAndSizes ( CandleSizeSku, CandleScentSku )
SELECT qryScentsAndSizes.CandleSizeSku, qryScentsAndSizes.CandleScentSku
FROM qryScentsAndSizes LEFT JOIN tblScentsAndSizes ON (qryScentsAndSizes.CandleScentSku = tblScentsAndSizes.CandleScentSku) AND (qryScentsAndSizes.CandleSizeSku = tblScentsAndSizes.CandleSizeSku)
WHERE (((tblScentsAndSizes.ssid) Is Null));

Save that with the name "add_tblScentsAndSizes and run it whenever a scent or a size gets added. It will add the appropriate records to tblScentsAndSizes along with an ID number for it.

 

[The_Doc_Man]

plog's comments are spot-on. Using a query to generate the numbers is totally unreliable because the order in which the table is presented is also not predictable. It is a side-effect of the fact that Access is based on SET theory, where all records in a set are (in theory) treated in parallel. (I.e. acts as though there is no first or last record.)

His suggestion of using an autonumber is as good as any other, but with this warning. Autonumbers cannot be guaranteed to be contiguously numbered. For any of several reasons, there can be gaps. IF there is any accounting or regulatory reason for the numbers to be contiguous with no gaps, an autonumber won't work either. If that applies to your case, come back to us with that information so we can suggest something else.

 

[nicko14]

A query can create unique numbers for each record you have, but those numbers are not guaranteed to remain with the same record each time you run the query. If you add a size or a or a scent to your database the next time you run the query the numbers for the entire set would be off from the last time you run it. So a query is not the answer.

I think you need to make a table of all scent/size combinations and add an autonumber to it. Here's the steps:

1. Make a query using this SQL:

SELECT CandleSizeSku, CandleScentSku FROM tblCandleSize, tblCandleScent

Paste it into a new query object and call it something like "qryScentsAndSizes".

2. Make another query based on qryScentsAndSizes. Bring down both fields into the query then change it to a MakeTable and make a table called "tblScentsAndSizes". Run it and you can now discard this MakeTable query.

3. In Design View of tblScentsAndSizes add a field called "ssID", make it an autonumber and make it the primary key. Save the table, then open it to see the numbers for each record.

That creates the unique numbers you need for all the scents and sizes you have. Lastly you should create a query to find and add records to tblScentsAndSizes for when you add a scent or size. This is that query:

INSERT INTO tblScentsAndSizes ( CandleSizeSku, CandleScentSku )
SELECT qryScentsAndSizes.CandleSizeSku, qryScentsAndSizes.CandleScentSku
FROM qryScentsAndSizes LEFT JOIN tblScentsAndSizes ON (qryScentsAndSizes.CandleScentSku = tblScentsAndSizes.CandleScentSku) AND (qryScentsAndSizes.CandleSizeSku = tblScentsAndSizes.CandleSizeSku)
WHERE (((tblScentsAndSizes.ssid) Is Null));

Save that with the name "add_tblScentsAndSizes and run it whenever a scent or a size gets added. It will add the appropriate records to tblScentsAndSizes along with an ID number for it.

Awesome thank you so much!

 

[nicko14]

plog's comments are spot-on. Using a query to generate the numbers is totally unreliable because the order in which the table is presented is also not predictable. It is a side-effect of the fact that Access is based on SET theory, where all records in a set are (in theory) treated in parallel. (I.e. acts as though there is no first or last record.)

His suggestion of using an autonumber is as good as any other, but with this warning. Autonumbers cannot be guaranteed to be contiguously numbered. For any of several reasons, there can be gaps. IF there is any accounting or regulatory reason for the numbers to be contiguous with no gaps, an autonumber won't work either. If that applies to your case, come back to us with that information so we can suggest something else.

They do not need to be contiguously numbered, the solution provided is perfect. Thanks so much

 

[oldaf294]

I know this has been marked Solved. Thought this might be useful.

Untitled

 

[Pat Hartman]

Here is a sample database that generates custom numbers which it looks like you are looking for.