Help with table design

Hi,

Recently, I start a small database for my shop selling electrical
appliances...

The database run fairly good, but still a have a question.

Some items had two pricing, one for retail and one for package (eg Electric
wire sold in roll of 50m or retail for 10 or 15 mt). How do I defines
multiple pricing for this purpose (possibly when user select one item,
Access should ask for choice of SalePrice?).

I have 3 tables below

tblItems
ItemID, BarCode, Description, SalePrice

tblTransaction
TransactionID, TranDate, ReceiptNum

tblTransactionDetails
TransactionID, ItemID(FK),Qty,UPrice, Discount(%)

Appreciate for any advice from this NG

SF

suggest you split your tblItems into two tables, and add an additional
table:

tblItems
ItemID (primary key, i assume)
BarCode
Description

tblPriceTypes
TypeID (primary key)
TypeName (such as "Retail", "Package", "Christmas Sale 2005", whatever you
need)

tblItemPrices
PriceID (primary key)
ItemID (foreign key from tblItems)
TypeID (foreign key from tblPriceTypes)
Price

each item in tblItems can have as many prices as you need to assign. when a
record is entered in tblTransactionDetails, you can pick the "correct" price
from tblItemPrices - filtered to show only the prices assigned to that item.
(you may want to continue to "hard-code" the price into each record in
tblTransactionDetails, so that you don't have to create a more complex setup
to accommodate changes in the "standard" prices of an item.)

hth


"SF" wrote in message
...
Hi,

Recently, I start a small database for my shop selling electrical
appliances...

The database run fairly good, but still a have a question.

Some items had two pricing, one for retail and one for package (eg
Electric
wire sold in roll of 50m or retail for 10 or 15 mt). How do I defines
multiple pricing for this purpose (possibly when user select one item,
Access should ask for choice of SalePrice?).

I have 3 tables below

tblItems
ItemID, BarCode, Description, SalePrice

tblTransaction
TransactionID, TranDate, ReceiptNum

tblTransactionDetails
TransactionID, ItemID(FK),Qty,UPrice, Discount(%)

Appreciate for any advice from this NG

SF

Thank you for your prompt response. I appreciate it.

SF

"tina" wrote in message
...
suggest you split your tblItems into two tables, and add an additional
table:

tblItems
ItemID (primary key, i assume)
BarCode
Description

tblPriceTypes
TypeID (primary key)
TypeName (such as "Retail", "Package", "Christmas Sale 2005", whatever you
need)

tblItemPrices
PriceID (primary key)
ItemID (foreign key from tblItems)
TypeID (foreign key from tblPriceTypes)
Price

each item in tblItems can have as many prices as you need to assign. when
a
record is entered in tblTransactionDetails, you can pick the "correct"
price
from tblItemPrices - filtered to show only the prices assigned to that
item.
(you may want to continue to "hard-code" the price into each record in
tblTransactionDetails, so that you don't have to create a more complex
setup
to accommodate changes in the "standard" prices of an item.)

hth


"SF" wrote in message
...
Hi,

Recently, I start a small database for my shop selling electrical
appliances...

The database run fairly good, but still a have a question.

Some items had two pricing, one for retail and one for package (eg
Electric
wire sold in roll of 50m or retail for 10 or 15 mt). How do I defines
multiple pricing for this purpose (possibly when user select one item,
Access should ask for choice of SalePrice?).

I have 3 tables below

tblItems
ItemID, BarCode, Description, SalePrice

tblTransaction
TransactionID, TranDate, ReceiptNum

tblTransactionDetails
TransactionID, ItemID(FK),Qty,UPrice, Discount(%)

Appreciate for any advice from this NG

SF

you're welcome


"SF" wrote in message
...
Thank you for your prompt response. I appreciate it.

SF

"tina" wrote in message
...
suggest you split your tblItems into two tables, and add an additional
table:

tblItems
ItemID (primary key, i assume)
BarCode
Description

tblPriceTypes
TypeID (primary key)
TypeName (such as "Retail", "Package", "Christmas Sale 2005", whatever
you
need)

tblItemPrices
PriceID (primary key)
ItemID (foreign key from tblItems)
TypeID (foreign key from tblPriceTypes)
Price

each item in tblItems can have as many prices as you need to assign.
when
a
record is entered in tblTransactionDetails, you can pick the "correct"
price
from tblItemPrices - filtered to show only the prices assigned to that
item.
(you may want to continue to "hard-code" the price into each record in
tblTransactionDetails, so that you don't have to create a more complex
setup
to accommodate changes in the "standard" prices of an item.)

hth


"SF" wrote in message
...
Hi,

Recently, I start a small database for my shop selling electrical
appliances...

The database run fairly good, but still a have a question.

Some items had two pricing, one for retail and one for package (eg
Electric
wire sold in roll of 50m or retail for 10 or 15 mt). How do I defines
multiple pricing for this purpose (possibly when user select one item,
Access should ask for choice of SalePrice?).

I have 3 tables below

tblItems
ItemID, BarCode, Description, SalePrice

tblTransaction
TransactionID, TranDate, ReceiptNum

tblTransactionDetails
TransactionID, ItemID(FK),Qty,UPrice, Discount(%)

Appreciate for any advice from this NG

SF