2 questions

[TJones]

I am setting up a simple "cash register" program using
Access. I am not well schooled in Basic but know my way
around macros okay.

First I have tables which contain the items and thier
current prices. I need to be able to save not only the
item number for each thing ordered but also the price
since the prices at some time will change and I need to
know what the price was at the time I actually sold each
item.

Second, I have two prices for each item - a regular price
and a special price. What is the best way to handle
this? Should I have 1 table that has each item but has
Price 1 and then TheBeginningTime and EndingTime for
Price 1 and then Price 2 and the Beginning and Ending
Times for that price as well.... or is there another way
of doing this?

Thanks for any an all help

 

[Allen Browne]

A1 Lookup price
For an example of how to do this, open the Northwind sample database.
Open the Orders Subform in design view.
Look at the AfterUpdate event of the ProductID combo.
It uses DLookup() to get the price.
If you need more help on DLookup(), see:
http://allenbrowne.com/casu-07.html

A2 Special prices.
Best to do this with a related table. Over time, you may have several
entries for one item, so there is a one-to-many relation between product and
specials.

The Special table will have fields like:
ItemID Number which product (foreign key to Item table)
StartDate Date/Time when the special starts
EndDate Date/Time when the special ends
Amount Currency the special price.

It could happen that this table has overlapping specials for the same
product, so to get the minimum price for a date you would DMin() rather than
simply DLookup(). This kind of code:

Dim varAmount As Variant
Dim strWhere As String
strWhere = "(ItemID = " & [ItemID] & ") AND (" & _
Format([SaleDate], "\#mm\/dd\/yyyy\#") & _
" Between [StartDate] And [EndDate])"
varAmount = DMin("Amount", "Special", strWhere)

 

[TJones]

Thanks, I'll give em a try!

Tammie