Setting a fixed price for different quantities of product

[CJ_London]

So you are using a barcode number as a PK?- it may be that it (or for some records) out of range of a long, Suggest provide the screenshot of your relationships, A PK's only purpose is to uniquely identify a record and should serve no other purpose - which sounds like you are in that you are using a barcode, this should be in a separate field

 

[raziel3]

Yes, I was using the Barcode as the PK. I have to go back and fix all my tables . Double allows for 15 digits and I might get 15 digit barcodes in my Product listing in the future so I will leave it as Double. Unless that's a bad idea??

 

[Pat Hartman]

1. I haven't worked with barcodes in a while. I don't know if they could ever contain letters but it doesn't matter. Just because a field might contain only numbers doesn't mean that it should be treated as a number. Phone numbers, Zip Codes,, Credit Card Numbers, and Social Security numbers all contain only numeric characters but since we don't do arithmetic with them, they should be stored as strings. That way you won't run into the problem you have with the limitations on the size of an integer. If the strings are fixed length, no problem but if they can be variable length, you might want to zero fill them so that they actually sort correctly.

2. The queries as you wrote them will return multiple records. This will not work. The simplest solution is to use an end date far in the future. When you add a new price, you update the end date on the current record before you add the new record. Having bot start and end allows you to use Between:
From tbl1 inner join tbl2 on tbl1.ProductID = tbl2.ProductID and tbl1.Quantity = tbl2.Quantity
Where SaleDate Between StartDate and EndDate

3. I guess you decided to ignore my advice on using different product codes for "packs" or using a range for the quantity. Good luck with that also.

 

[raziel3]

3. I guess you decided to ignore my advice on using different product codes for "packs" or using a range for the quantity. Good luck with that also.

I may have to use this because even though I set up a price for 24 units (1 Case) I did not take into account 48 units or 36 units. So I will have to change my Products to have a separate PK and make UPC a Short Text to include things like "Case", "Packs" etc. Or have a separate column to have the Package Amount.

 

[arnelgp]

I really messed up my table design in the beginning,

i think you are really in mess.

when a customer get "12" from loose items, you tend to input them as "pack", as "others" suggests.
i have been a merchandiser in a supermarket before and non those suggetions make sense in real
world.
when we make "pack/promo" items, they are treated differently. they made up of special upc.
and as such they are in separate selling area, already in "packs" or "bundled".

 

[Gasman]

Crossposted?on 31/07/21 https://www.accessforums.net/showthread.php?t=84113

 

[Gasman]

i think you are really in mess.

when a customer get "12" from loose items, you tend to input them as "pack", as "others" suggests.
i have been a merchandiser in a supermarket before and non those suggetions make sense in real
world.
when we make "pack/promo" items, they are treated differently. they made up of special upc.
and as such they are in separate selling area, already in "packs" or "bundled".

Yes, in fact a lot of times I have seen 'Not to be sold separately?' on such items in the pack.?

 

[arnelgp]

Crossposted

boom! there can only be one!

'Not to be sold separately?' on such items in the pack.?

 

[June7]

When I set UPC field as Long Integer, Access would not accept the data "wrong type", setting as Double displays as scientific notation. I set field as text.

 

[raziel3]

Crossposted?on 31/07/21 https://www.accessforums.net/showthread.php?t=84113

What's the issue here?? Is not the purpose of the Internet to do research? Why limit yourself to one forum? Maybe, just maybe there are other developers out there who can offer insight and different ideas. Or is it that some members in this forum feel that you shouldn't go all over the internet asking the same question.

If a doctor gives a diagnosis won't you get a second opinion? If you want to buy a car won't you look around? Or will you scan the same lot and settle for what they have?

Thanks @Gasman for your support . Real nice belittling people who don't know anything and just want to learn.


Anyways, @arnelgp and @Pat Hartman I will definitely be adding different barcodes/ProductIDs to my Products table for Cases/Packs etc. You were right it is much more manageable like that.

 

[Gasman]

Nothing wrong with crossposting per se.
Just that it is forum etiquette to advise of the crossposting (which I have now done for you), as well as good manners.
That way members of any forum, who all give their time up freely, do not waste their time offering the same advice.

Not belittling you at all, so you can take that particular chip off your shoulder for a start!

 

[raziel3]

Nothing wrong with crossposting per se.
Just that it is forum etiquette to advise of the crossposting (which I have now done for you), as well as good manners.
That way members of any forum, who all give their time up freely, do not waste their time offering the same advice.

Not belittling you at all, so you can take that particular chip off your shoulder for a start!

Oh, the tone of your post sounded like you were making fun of me. My apologies.

I'm getting so many different ways how to go about this. On youtube,

, Richard Rost used a Short Text data type for his UPC but you've learned from the start that a field that contains only numbers warrants a Numeric Datatype.

Then the Standard Barcode is 13 digits but it has products with 14 and 15 so I cannot use the Long Datatype. There are scanners that scan up to the 13 digits no matter the length but the 2 that I have scans the entire code.

Then @CJ_London suggested the End Date go to in the Products Table but then if you run a Promotion on a particular Item, for example, BOGO sale that will run for a week. That EndDate may belong in the Prices Table.

Then the use of different barcodes for Bulk Items. I was tracking everything by each unit so it would be easier to do Stock Checks, but now that 1 case is equal to 24 units I'll have to do a separate column in my Products table to show the packing amounts and tie that back in to my Quantity on Hand Query.

 

[Pat Hartman]

Richard Rost used a Short Text data type for his UPC but you've learned from the start that a field that contains only numbers warrants a Numeric Datatype.

Just the opposite. If you are not going to do arithmetic with a "number", it should be stored as text. a Bar Code is a CODE, not a number. The code just happens to contain only numeric characters but it is not limited to only numeric characters.

All numeric data types have limits on the size of the value they will hold. For example:
Integer max = 2,147,483,647
Floating point max = 100,000,000,000

The old version of the ISBN barcode is 10 digits but the new version is 13. The old version would fit in a Floating point field but only some would fit in an Integer. The new version would not fit in either of these numeric data types.

The point is - YOU do not control the format of these CODES (not numbers) and just because they happen to be numeric today, doesn't mean they will be numeric tomorrow. This is why Richard Rost used a text data type. It is called defensive programming and all professionals practice it.

 

[CJ_London]

Then @CJ_London suggested the End Date go to in the Products Table but then if you run a Promotion on a particular Item, for example, BOGO sale that will run for a week. That EndDate may belong in the Prices Table.

perhaps - all depends on what rules you want to apply. Personally I would put into a promotions table (which will have start/end dates) which overrides the prices table for the period of the promotion. Once the promotion ends, normal pricing resumes. Your way you need to enter information twice, once for the promotion and again once the promotion ends to restore the price.

Any product you have will always have a price (even if you are giving it away, it still has a price of 0), the only time a price won't apply is when you are not selling the product anymore - so that is where the end date goes, with the product.