CODE 911

[Guest]

I am trying to get a price to list in a field of a form based on the value of
a field called PACKINGOFITEM - My code is:

Private Sub Text31_LostFocus()
If PACKINGOFITEM = "EACH" Then
Text31 = WIEACHPRICE
ElseIf PACKINGOFITEM = "PACK" Then
Text31 = WIPACKPRICE
ElseIf PACKINGOFITEM = "BOX" Then
Text31 = WIBOXPRICE
Else
End If
End Sub

Nothing appears in my field. I used an unbound text box for my field that I
want the information to go to. What am I missing?

Thank You

 

[Guest]

Hi

You code should look something like this

If Me.PACKINGOFITEM = "Each" Or "Pack" Or "Box" Then
Me.Text31 = "WIEACHPRICE"
End If

This cuts yoru code down from this (which is what you code was)

Private Sub Text31_AfterUpdate()
If Me.PACKINGOFITEM = "Each" Then
Me.Text31 = "WIEACHPRICE"
ElseIf Me.PACKINGOFITEM = "PACK" Then
Me.Text31 = "WIPACKPRICE"
ElseIf Me.PACKINGOFITEM = "Box" Then
Me.Text31 = "WIPACKPRICE"
End If


BUT
It is not clear from your post what you are refering to. - What is
"WIEACHPRICE". In the code above I have assumed they are text. But you say
you want a price "list".

You will need to supply a little more information to get a better answer.

 

[Guest]

WIEACHPRICE = Wichita Each Price and is a currency value. Each of our items
may have once individual price or may have a individual price, pack price,
and box price. And each area we are located has a different price lever.
For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE, etc.

 

[John W. Vinson]

WIEACHPRICE = Wichita Each Price and is a currency value. Each of our items
may have once individual price or may have a individual price, pack price,
and box price. And each area we are located has a different price lever.
For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE, etc.

WHOA! Are these the names of table fields? If so you are WAY off track.

For one thing, you can't just name a field in VBA code and pull data from it.
More importantly, if you are storing data (the locale) in fieldnames, you're
in for heaps of trouble.

Where are these currency values stored? What is the structure of your tables?

John W. Vinson [MVP]

 

[Tom Wimpernark]

i'm not sure that his design really is that bad

talk more about your design; this would be appropriate in certain
circumstances

 

[Guest]

I have a master database where the tent# and locations are stored . The next
data base for repacks has a linked table from the 1st - as to reduce
duplicating changes with the locations as they can fluctuate from year to
year.

In the data base I am working on I have A price list table with has the
prices of four levels and in each level their is a possiblity of four prices
per item- as we can not sell a box of items asbox if they have been broken
into individuals. I am sure you have been to a fireworks tent and seen the
novelty items in boxes, or as individuals.

I then have a table that assigns the box number (repack sticker bar code) to
each tent. Each tent may have 1 return box or dozens. Some of our buildings
return close to a 100 boxes each season.

I then have a table where I enter the detail (contents) of each box. Each
box may have a few as 1 item or dozens of items. This table's fields are the
box # (which must match a box number listed in the assigned box number table
I just mentioned) this is to avoid incorrect box numbers from being entered.

Then there is an item description field (this field is a look up from
another table named products master table that includes barcodes, item
descriptions, brand names etc.). The next field is an autofill find that
brings up the item number based on the product master table and what is
entered in the product description field.

The next field is the item quanity (how many are being returned). The next
field of this table is the item packaging (how is it being returned).

Once all of the box details are entered I quirey the Box Detail Table and
Price List Master Table so that I can match the price level (i have a
separate table to determine which code = which price level and assign the
code to each tent).

My obstacle at this point is the best way to get the correct price for each
item so that I can take that price and multiply it by the quanity returned.
My boss wants the report to look something like this:

#8 Assorted Cones 45 Each $3.95 $177.75
Jumping Jacks 14 Packs $3.90 $ 54.60
Jumping Jacks 97 Each $0.20 $ 19.40

And I need to be able to sort the repacks by tent, box number, or item
description.

The prices WILL NOT CHANGE - because the prices are already stickered to the
product the next season we will sell it for what the sticker says as our
operators will not have the time to reprice all the items. So box 567980
whose total value is $245.67 will stay $245.67 next year. Once box 567980 is
opened next year and the product put in another tent it will essentailly
cease to exsist.

As you can imagine - I am pulling my hair out. I have some ACCESS skills,
and am learning programming, but alot of what I have accomplished is by
reading two books I have, asking questions on this web site, reading others
questions, and mostly trial and error. So any help is appreicated.

 

[Guest]

Why would naming the fields like this be off track? I am giving each field
the name of what it is for example

WIEACHPRICE for ITEM #8 Cones would 3.79
WIEACHPRICE for ITEM Jumping Jacks would be .39
WIEACHPRICE for ITEM Dymand Duster would be 59.99

were as

LAEACHPRICE for ITEM #8 Cones would be 3.99
LAEACHPRICE for ITEM Jumping Jacks would be .59
LAEACHPRICE for ITEM Dymand Duster would be 69.95

Is there a better way to keep track of different priced levels that may have
up to 4 prices per each item per level? I will take any suggestions to make
this easier.

 

[John W. Vinson]

Why would naming the fields like this be off track? I am giving each field
the name of what it is for example

WIEACHPRICE for ITEM #8 Cones would 3.79
WIEACHPRICE for ITEM Jumping Jacks would be .39
WIEACHPRICE for ITEM Dymand Duster would be 59.99

were as

LAEACHPRICE for ITEM #8 Cones would be 3.99
LAEACHPRICE for ITEM Jumping Jacks would be .59
LAEACHPRICE for ITEM Dymand Duster would be 69.95

Is there a better way to keep track of different priced levels that may have
up to 4 prices per each item per level? I will take any suggestions to make
this easier.

If you have a field for each location, you're "committing spreadsheet". What
do you do if you need to add a new location? or remove one? or one large area
location splits into two? Redesign your table, all your queries, all your
forms, all your reports? OUCH!

If each Location has many Items, and each Item can be sold in many Locations,
you have a many to many relationship. The proper way to handle this is to use
three tables:

Items
ItemID <Primary Key>
Description
<other fields about the item as an entity>

Locations
LocationID <Primary Key>
Location <text, e.g. Wichita, Louisiana, Tahiti>

Prices
ItemID <link to Items>
LocationID <link to Locations>
Price <the price of this item at this location>

and perhaps also an EffectiveDate and ThroughDate field so that you can keep a
historical record of what prices were in effect at what time.


John W. Vinson [MVP]

 

[Guest]

But that is what I have done. I have a separate table as follows:

Locations Master - in a separate database with other items but table is
linked in this data base

Prodcut Master - each item, description, case count, etc.

Price Master - look up field for item #, then each price level with each
packing level price.

Please explain more about the effective dates, as that is one of my next
obstacles, write now I am looking at creating a data base for each year - I
don't like that idea -to much can get lost in translation so to speak.

Thanks for the help

 

[John W. Vinson]

But that is what I have done. I have a separate table as follows:

Locations Master - in a separate database with other items but table is
linked in this data base

Prodcut Master - each item, description, case count, etc.

Price Master - look up field for item #, then each price level with each
packing level price.

In your previous message you said:

=====
Why would naming the fields like this be off track? I am giving each field
the name of what it is for example

WIEACHPRICE for ITEM #8 Cones would 3.79
WIEACHPRICE for ITEM Jumping Jacks would be .39
WIEACHPRICE for ITEM Dymand Duster would be 59.99

were as

LAEACHPRICE for ITEM #8 Cones would be 3.99
LAEACHPRICE for ITEM Jumping Jacks would be .59
LAEACHPRICE for ITEM Dymand Duster would be 69.95
====

What I'm suggesting is quite different. Rather than a *FIELD* named
WIEACHPRICE, you would have a *separate record* for the Wichita price. E.g.

Product Master
123 #8 Cones
314 Jumping Jacks
225 Dymand Duster

Locations Master
2 Wichita
3 Louisiana
5 West Texas

Pricelist
ProductID Location Price EffectiveDate EndDate
123 2 3.79 8/1/2006 <null>
123 3 3.79 8/1/2006 10/14/2006
123 3 3.99 10/14/2006 <null>
225 2 59.99 5/12/2007 <null>

In this example the price of #8 Cones (item 123) went to $3.79 on 8/1/06 in
Wichita, and since the end date is null it's still at that price; in Louisian
it also was at $3.79 up until October 14, when it went to $3.99 where it is
now.

You can use a Query to find the most current price for any item in any
location from this table; you can use another query to find what the price was
as of September 1, 2006 using date ranges; etc.

Please explain more about the effective dates, as that is one of my next
obstacles, write now I am looking at creating a data base for each year - I
don't like that idea -to much can get lost in translation so to speak.

Storing data (date ranges) in a database name is even *worse* than storing it
in fieldnames - so your aversion to this idea is well founded!

Am I misunderstanding your actual structure? What are the field definitions of
Locations Master and Price Master in your current database?

John W. Vinson [MVP]

 

[Guest]

John,

I tried to answer you earlier but got locked out - and being in the
fireworks business I am sure you understand crunch time is here for me.

I have three databases

The first is TENT MASTERS
In this db I have a LOCATION MASTER table that consist of the following:

Tent # - the four digit number we assign each location

Tent Location - this is the city of each location and if necessary a
descriptive suffix such as 101 Wichita - E Edmund or 2100 Topeka - Smoke Shack

City

St

Company Code - this is either 1, or 2 depending on which company the
location is for

Dillon - yes or no field for a special lease

I have other tables in this database that do not pertain to the repacks

My Second database is the Repack Inventory Master db

I have several structure tables (what I call tables that help establish
codes, categories etc.)

My main tables are PRODUCT MASTER with the following fields

MFBARCODE - manufacture bar code
INTERNAL# - our number we assign each product based of the last 5-9 digits
of the barcode
ITEMDESCRIPTON - #8 Assorted Cones, 10" Golden Bamboo Sparklers, etc.
CASECOUNT- quantity count in this form 80/20/100
BOXCOUNT - how many come in a box ex: Aladdin’s Lamp 24/2 2 per box

The next main table is PRICE MASTER with the following fields

Item#-Look up on the Internal# in the above table
LAEACHPRICE
LAPACKPRICE
LABOXPRICE
LABRICKPRICE

This repeats for WS, JK, WI, KS each having four fields - the each price is
the individual price of each item, the pack price is the pack price of each
item, the box price is the box price of each item, and the brick price is the
brick price of each item, again by Price level (LA, WS, JK, WI, KS).

The process of repacks goes like this:

I issue to each location a series of bar code numbers from printed labels.
At the end of the season any reaming inventory that has been opened is
counted and listed on these sheets. The list the bar code, item description,
quantity being returned and the packing of the return.

Each box may have one or many items. Each location may return on or many
boxes. And each item may be returned in on of four pickings.

It is my job to enter all of these items and produce reports that show 1.
What each tent returned 2. The value of the whole return by tent 3. The value
of each box 4. The value of all items separated by other items.

As I mentioned the prices do not change once a box is entered. This box
then is staged on a skid. The skids are staged in no particular order; I may
have boxes from ten tents on one skid or all the returns from one tent on a
skid. So I have to issue the repacks again to the appropriate skid and
produce a report showing what is on each skid.

Then each skid will be issued to a location at the beginning of next season.
The operators will open these boxes and sell what is in them. The boxes
themselves may be reused, but the labels will be covered up with new labels
indicating a different list of items being returned. So essential, the old
box number dies.

I hope this info helps; I only have a few weeks before I have to be ready to
roll on this.

Thanks for your help.

Lynette

 

[John W. Vinson]

The next main table is PRICE MASTER with the following fields

Item#-Look up on the Internal# in the above table
LAEACHPRICE
LAPACKPRICE
LABOXPRICE
LABRICKPRICE

This repeats for WS, JK, WI, KS each having four fields - the each price is
the individual price of each item, the pack price is the pack price of each
item, the box price is the box price of each item, and the brick price is the
brick price of each item, again by Price level (LA, WS, JK, WI, KS).

And I still assert that this design IS WRONG, and is the cause of your
problems.

Reread my posts.

A better - I'd say MUCH better! - design would have:

Internal# (I'd recommend *never* using Lookup fields)
Location <link to Tent Masters, as a linked table>
UnitSize <e.g. Each, Pack, Box, Brick>
Price

Rather than twenty price fields, you have *ONE* price field - with
twentyrecords. If you add a sixth location (surely that's possible, someday??)
you will have to redesign your table, redesign all your queries, redesign your
forms, redesign your reports, redesign all your calculations; with the
normalized design you *add one record to the Locations table* and you're DONE.

Do you see what I'm saying?

John W. Vinson [MVP]

 

[Guest]

Currently I only add the new locations to the Location Master - I don't have
to change everything.

How can I put the prices in one field with many records and still keep them
separate? The price levels may change from each location each year - for
example Little Beruit - last year was JK price level, but this year may well
be LA price level. Although once the price are entred for a year they do
not change.

Evidently I am missing basic structure knoweledge. Do you recommend any
books or other resources to learn about setting up a well designed db?

I really appericated all your suggestions and advice.

 

[John W. Vinson]

Currently I only add the new locations to the Location Master - I don't have
to change everything.

Unless I'm misunderstanding, you DO need to do two things: you need to add a
new record (Biloxi let's say) to Location Master; and you need to add *four
new fields* to the Price Master table to record.

How can I put the prices in one field with many records and still keep them
separate?

You're keeping them together. My suggested normalized design *KEEPS THEM
SEPARATE*. Each price is in *its own record*, separate from all the other
prices. You pull up only the one price that you need.

The price levels may change from each location each year - for
example Little Beruit - last year was JK price level, but this year may well
be LA price level. Although once the price are entred for a year they do
not change.

You would have a record in the normalized table for each distinct combination
of Item, Location, Packsize, and (if you wish) year or date range. You can use
a Query searching for Little Beruit for #8 Cones in the Each price, by *simply
searching for those values in the table* to find the record containing that
price.

Evidently you're still "thinking spreadsheet" and assuming that unless all
four prices are in the same record that you can't get to them. That assumption
is at the root of your difficulty, because it's wrong!

Evidently I am missing basic structure knoweledge. Do you recommend any
books or other resources to learn about setting up a well designed db?

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

especially the "Database Design 101" links on Jeff's page.

John W. Vinson [MVP]