Adding prices to "products" in a database

[mig144]

Dear List,

I have made a database of locations. Each location has a subform which lists the "products" at this location. There are only about 5 sorts of product, and these are in a look-up table.

I would like to add the price of these products, so that when I open a different form/report, I can see how much money is spent at each location, etc etc. When I add a new product to the list at the location, the price automatically is logged somewhere on the database. I would also like to add another product, which is delivery. The price shouldn't be visible on the subform of the location form.

The thing that bothers me, is that the database is already filled with data. How can I add these details so that existing products have prices attached to them without exporting and reimporting all the data? There are other linked tables which would suffer.

I've tried to look at the sample databases on Access but I am, sadly, none the wiser.

Many thanks,

Mrs Miggins

 

[Len Boorman]

If the database has been correctly designed (Normalised) then it should be possible to add additional fields to existing tables or indeed new tables completely and not affect any part of the existing interfaces.. This is Ansi Sparc 3 Level Architecture (Think spelling is okay) You say that price is already stored in database. You should not repeat this data but extract it from its current table.

Again new data fields may be added but be aware that if you change the forms that you use to show this new data and anybody else uses the same form then they will see the new data as well, because they are using the same form.


Export and import should not be necessary at all. You will find that if you want to add fields to the link tables you must go back to the source database. Similarly on Access 2000 certainly and probably above you may need exclusive use to make amendments

L

 

[mig144]

A price to pay

Len,

Thanks for replying.

A price is not already stored in the database. I am a little unsure of which table to add it to. I guess I would add it to the table of products, but as it is relevant to the look-up table for the product field, should I replace the look-up table with a separate linked table?

I have tried to put it in a different table. Please could you advise?

I have three tables, Address, Product, and Correspondence. Address to Product is one-to-many, Address to Correspondence is one-to-many.

The items in the Product Table are:
Product ID
Address ID
Product Type
Product Reg No.
Date Requested
etc etc

Thanks, this is very kind of you.

Mrs Miggins

 

[Len Boorman]

I assume that within the Product Table Product ID is the primary key although I have a question as to where the product Description resides or is the Primary Key both Product ID and Address ID.

Now is it possible that Product may be obtained from more than 1 address. I ask these questions because a number of solutions are possible but each is correct for only a particular circumstance.

If the Price potentially varies depending upon Address ID then price should go with ProductID and Address ID. If it is a single price regardless then I would normally expect it to be along with the other details of the product.

HTH

Len

 

[mig144]

The Price is Right

Len,

The ProductID field in the product table is the primary key. The AddressID is the foreign key.

The products are being delivered to the addresses, not obtained from them. It's more like an inventory at each address. The price is the same for each product wherever it goes, likewise with delivery, which is also a set price per item, and does not differ according to which item is delivered.

I'd send you the whole database zipped, but I don't want to breach confidentiality!

Again, many thanks for taking the time to advise me.

Mrs Miggins.

 

[Len Boorman]

The Price of a specified product is an attribute of a product. Therefore the Price should without doubt be included in the Product table. In your situation the Price is fixed and is not dependant upon any other factor.

When you have products associated with more than 1 supplier type of situation then the price is dependant upon Product and Supplier (or Address in your case). That is why I asked about the primary key.

I think that the Delivery cost will also fall into the same situation as the Product price

Len

 

[mig144]

Vincent Price

so, I can add a new field to the Product table called Price. I shall do this now.

But how do I make it so that, e.g., whenever Product Type A is entered into the subform field, then £183 always automatically pops into the data somewhere?

 

[Len Boorman]

In the Products table against each item you will now have a price.

Not exactly sure what you are doing but.

The si=ubform will have a "source" probably a query. Include the field Price in this query and then you can also add this field to your subform.

I believe that addingProduct 1 will then automatically drag the price in the Price field

L

 

[mig144]

Thanks for your reply.

I don't want the price in my subform. I want the price to be invisible until I devise a report which asks for it.

I also want the Delivery to be absent from the subform. I suppose there is a query which will filter out "Delivery" from the subform. This is another matter, and a simpler one at that.

Surely if I just have a field called Price, I will have to fill it in manually. I'm imagining there is some magic formula for "when I type the word 'Basket' in the field 'ProductType', '£183' will automatically appear in the field 'Price'"

This is why I thought there might have to be a new table which links Price to Product Type.

 

[Len Boorman]

Thanks for your reply.

I don't want the price in my subform. I want the price to be invisible until I devise a report which asks for it.

Thats fine just do not include the field on the subform

I also want the Delivery to be absent from the subform. I suppose there is a query which will filter out "Delivery" from the subform. This is another matter, and a simpler one at that.

Thats okay too

Surely if I just have a field called Price, I will have to fill it in manually. I'm imagining there is some magic formula for "when I type the word 'Basket' in the field 'ProductType', '£183' will automatically appear in the field 'Price'"

This is why I thought there might have to be a new table which links Price to Product Type.

Now the Price has to be got from somewhere. You know Basket has a price of £183 but the database does not !. So it has to be told but only once for each Product.

You did say "A price is not already stored in the database" so how is it to know.

A few bits just to make sure you understand what I believe is appropriate to your database

Product A particular item
Product Type. A catagory for grouping Products.

So a Particular Basket has a price of £183, Another Basket (different basket) has a price of £85.

Both of these baskets are within the Product Type "Basket" (or similar).

Len

 

[mig144]

Product A particular item
Product Type. A catagory for grouping Products.

Product = the name of the table
Product Type = the field containing the lookup table of products, lets say the list is "basket, bucket, trolley, delivery"

I have, since our correspondence, added a field to the Product table called "Price". How do I make it happen that when I choose "basket" from the drop down/lookup, Price = £183. When I choose "Bucket", Price = £205. How do I tell the database the price of each item? Previous experiments with linked tables for this problem have been disastrous.

 

[Len Boorman]

So the Product Table looks a bit like

Product Type Price etc
Basket
Bucket
Trolley


Put the Price in against the item

Product Type Price etc
Basket 183.00
Bucket 205.00
Trolley 100.00

Is the delivery price the same for all items ?.

Where is the dropdown list

If you are operating directly with the table the drop down list will purely reflect the items within that field.

If you are operating from a form then the source of the combo box becomes
select [Product Type],[Price]
from [Product Type];

Len

 

[mig144]

Len,

I've mostly cracked it, thanks to your help! I didn't realise I could have two fields in the Lookup Table! So now I have, it's working nicely.

The last part of the puzzle is to filter out the "Delivery" product type from the subform. In other words, I want to be able to enter the Delivery in a different form somewhere, but not have it show up on the subform of the mainform. How do I go about this?

I've made a query as the source for the subform which says "not Delivery" for the Product Type field but it won't allow new records to be added to the subform, even though I've told it "Allow Additions" - "yes" on the properties for the subform. That's no good, as it will be the place where additions are added!

 

[Len Boorman]

Are you using combo box for the selection of Product Type. If so then it is the source of the combo box that needs to have Not "Delivery" in the criteria.

The source of a combobox can also be a query.

Not sure at all why it is now not allowing you to add.

Must admit that normally when I am adding records I take a different approach.

I only use a main form and have combo boxes for fields wherever possible.

There can be issues if the Add procedure requires data tobe inserted into more than a single table

Beginning to get a little difficult to fully imagine application you are developing
but still thinking

L

 

[mig144]

I am using a lookup table for the selection of the product type. I could change it to a combo box, but I have no idea how queries work with combo boxes.

 

[Len Boorman]

Okay so a lesson on combo boxes.

This post went on for a bit so suggest you print it cos it is a bit long.

First make a complete copy of your database. Always best to do when attempting tweaks then if you really screw it up you can at least go back to a known situation.

Basically what we are going to do is replace whatever you have on the subform to select Product Type with a combo box.

So delete whatever you have on the sub form for the Product type.

No look at the tool bar. toolbox. along with all the things like text fields etc there is what looks like a magic wand. Make sure this is switched on.

Now click the combo box icon and then move the cursor to the sub form and draw click and hold down the mouse button and stretch out a small box. Size is not important because you can change it later.

Okay now the wizard should also have started. You can now select a table and then the fields you want in your combo box, Keep moving through the wizard

You will eventually get to a point where you are asked if you want tosave the data and here basically you say yes and then choose the field that you want to save the data to. This field is the table field where ypou want to store the data and must obviously be one that is included in the field list or query of the subform.

Okay so if you now right click the combo box and select properties (still in design view of the sub form you willsee a whole bunch of properties. Examine the row source and you will see that it looks just like sql. well that's exactly what it is and if you lick on the ellipsis at tyhe end of the row you will actually see the query in a form you are probably more familiar with. You can add sorting or parameters like not "Delivery" to teh query and these will be save back to the query row source in the raw sql.

You can also see that teh number of columns is shown together with teh bound column. This is the column that actually holds the bit of data you actually want to save.

Also are the column widths, You can change these if they are not suitable.

Little trick. If you set the column width tozero then it disappears to the user. Sometimes you actually want to save a bit of data that makes no sense to the user but the description of that item does, so you can set the column width to zero and the user only seen the description but you save the actual data that you want to save.

Thats a fair bit to type let alone read and have a go with so I will leave it for the time being.

See how you get on.

len