Adding Dataset to a record in my Form

[khwaja]

I have an orders form and I would like to add products and its compenents which are maintained seperately to my order. I was wondering how could I achieve this. Please note I maintain products/components seperately and these can change from one order to the other. So my first order will be different from the secod and I would like to take a snapshoot of products and components ordered. For now I am using make table query to achieve this but in a user friendly system, I would like them to add products and save the order.

 

[wiklendt]

sounds like you have not got a normalised arrangement. what you should do, is have a junction table for your orders. something like:

tbljOrders
------------
OrderID (PK)
ProductID (FK)
ComponentID (FK)

you would then be able to view historical data off all products/component combinations.

 

[khwaja]

Thanks. So I should base my form on a junction table? This usually does not work. You migt be right in your observation but if you can give me some guidance on how to set out and what table to use as the source for the form, I will be grateful.

 

[wiklendt]

Thanks. So I should base my form on a junction table? This usually does not work. You migt be right in your observation but if you can give me some guidance on how to set out and what table to use as the source for the form, I will be grateful.

i did - in my first post. and it can work if setup correctly. give me a few minutes and i'll put together a sample for you.

 

[khwaja]

Much much appreciate your help.

 

[wiklendt]

here is a quick-whip sample.

open the form frmOrderSearch.

you can select which customer you want to view from the dropdown.

then in the subform you can view or edit their orders. this subform is based off a query that sources its data from the JUNCTION TABLE of tblOrders.

the orders comprise of only a few options for a product, plus a few options for colour, plus a date field for reference. these can be in any combination you want. and as many can be added as you want.

obviously, you will have to alter bits to make it fit with your data/setup, but that's the basic idea of it.

 

[khwaja]

Many thanks. This gives me a fair idea to work off. Mine is a little complicated as I will need to identify components with each products. So treating your customer form as the product form, I can raise orders? But that's not what I want. Maybe I should I treat Customers as orders and add products but then how to add components? Somewhat confused. I have attched the database which now includes my three tables. Would you be kind enough to relate this to what you got. Meanwhile I will continue reviewing your tables.

Cheers

 

[wiklendt]

your tables are confusing.

i think you need to normalise a little more (i.e., you seem to have the same fields in many tables, like 'freight charge' (even twice in one table!), 'ILPO', etc). also you should have a seperate supplier table, where your 'supplier' field ought to be a FK in your product table.

plus it looks as though some fields are better handled by a query and/or report than a table (i.e., annual speding, Fxrate can be calculated from each of the records, etc)

not sure how different components can be part of the same product, unless you mean along th elines of "computer" with components "motherboard of this type" "RAM of that type" etc etc. still, even then 'computer' is going to be called something like dv2473xt or whatever to distiguish it from another 'computer' form the same supplier with different components.

i can't really tell from your tables how you want the data to fit together. for example, you seem to have you component table have product as FK - but i thought you said each product is made of several components? surely it should be the other way around.

not to mention your field names are jargon and to my non-shipping life-experience completely indecipherable, making it harder to make connections with your data.

start form the beginning: make normalised tables. i.e., a product has nothing to do with when it is ordered, or who bought it (and apple is an apple whether margaret buys it or tony buys is). suppliers are also seprate - suppliers can drop a product and another supplier pick it up.

so your tables already should be (from what i can glean off your tables)

tblProducts
tblOrders
tblSuppliers
tblCustomers (i.e., Buyer)
tblOwners....

the list goes on.

 

[khwaja]

You are right there are some duplication and redundant fields. I was aware of these and I left them there as I need to migrate existing data. I merely created a copy of the prod table for your review. All products have a set number of components and not the other way round. It is an internally used simple database which does not require details of suppliers except for their names. Yes, being unfamiliar wth the industry specfic jargon makes iot hard but in simple terms, I might just need some code to keep a snapshot of the order while retaining the product and component relationship with some variables such FX rate. May be I don't need a order table.

Appreciate your kind assistance on this. But will be happy if you could offer further guidance.

 

[wiklendt]

You are right there are some duplication and redundant fields. I was aware of these and I left them there as I need to migrate existing data. I merely created a copy of the prod table for your review. All products have a set number of components and not the other way round. It is an internally used simple database which does not require details of suppliers except for their names. Yes, being unfamiliar wth the industry specfic jargon makes iot hard but in simple terms, I might just need some code to keep a snapshot of the order while retaining the product and component relationship with some variables such FX rate. May be I don't need a order table.

Appreciate your kind assistance on this. But will be happy if you could offer further guidance.

at the moment, the only person who can help you is yourself.

you need to figure out exactly what data you want out of the database (i.e., what you want the database to do for you - e.g., track shipments, track products, track ....), which will help you decide what data to collect, and which data would be calculated rather than stored (basically anything that CAN be calculated from stored data should BE calulacted by query NOT stored again in a table). i cannot help you with that - only you know what data you have and what you want to keep from your original sets.

then you have to ask yourself how you want it all to fit together. you should read up on "data normalisation" (or "data normalization", as the yanks would say it) as much as possible - i didn't know a thing about normalisation when i created my first database and it made everything a massive nightmare - since fixed, but i wouldn't want to wish that experience on ANYONE! a great start on this would be the "search" tool on this forum.

start with those basics and see how you go. post back when you get stuck, but until you figure out what you want and need, no one here can really help.

 

[khwaja]

Appreciate. Will do.