How to create a unique number for products (1 Viewer)

uru85

New member
Local time
Today, 15:20
Joined
Dec 11, 2019
Messages
4
Hi there, I'm quite new in Access so I need some help!

I'm creating a form to manage purchasing items.
I have a table with the item code and the quantity.
But I want to create a unique number for each product that I bought. (I need to track each item in a process)
I was thinking to create another table with an AutoNumber, but I don't know how to say for "Xitem and quantity = 3" repeat Xitem 3 times creating 3 new ID for each item.

If you can help me with some suggestion will be great!
 

uru85

New member
Local time
Today, 15:20
Joined
Dec 11, 2019
Messages
4
Thanks for reply!
I know Autonumber! I'm using that, what I need is create a table like this:

Purchase_Table:

Purchase_ID: 50001 Item:X Quantity:2
Purchase_ID: 50002 Item:Y Quantity:3
Purchase_ID: 50003 Item:Z Quantity:2

Purchase_perItem_Table:

ID_perItem: 1000001 Purchase_ID: 50001
ID_perItem: 1000002 Purchase_ID: 50001
ID_perItem: 1000003 Purchase_ID: 50002
ID_perItem: 1000003 Purchase_ID: 50002
ID_perItem: 1000004 Purchase_ID: 50002
ID_perItem: 1000005 Purchase_ID: 50003
ID_perItem: 1000005 Purchase_ID: 50003

For each purchase paste Purchase_ID * Quantity times

I hope it's clear now.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,454
Hi. I'm afraid it's still not clear to me. Also, storing "calculated" values is against Normalization rules.
 

uru85

New member
Local time
Today, 15:20
Joined
Dec 11, 2019
Messages
4
Hi theDBguy!

Probably I'm facing in a wrong way, because my inexperience..

What I need is register a purchase, in which I'll specify products, quantity, etc. (this I already created)
Next I need to assignee a unique number per each product that I bought. Because I need to track specific item in next steps..

For example:
If i bought 3 Apples and 2 Oranges, i need this:

ID_perItem: 1 Product: Apple
ID_perItem: 2 Product: Apple
ID_perItem: 3 Product: Apple
ID_perItem: 4 Product: Orange
ID_perItem: 5 Product: Orange

So I have ID (1,2,3) for my 3 apples and (4,5) for my 2 oranges.

I hope this time make sense.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,454
And what's the next step after that? You can create a list/table like that using a cartesian query against a Tally table.
 

Users who are viewing this thread

Top Bottom