COMBINE 2 FIELDS IN A QUERY FROM SEPERATE TABLES????

[lomano]

I have 2 tables, a truck_compartment_table that has truck_number and compartment_size as fields, and trailer_compartment_table which has trailer_number and compartment_size as fields. I have to create a subform of an order form that needs to automatically show the compartment sizes, so i want to combine the compartment_size from the truck table with the compartment_size from the trailer table which will create one field. confused yet? hope not. not only that, i want to be able to change the data in these fields (the compartment size is used to show the capacity, and will be the default litres entered into compartment), and not affect the original data.

In addition to this, the client wants numbers beside each subform entry... 1..2..3..4 etc for each row of data to represent the compartment number.

HELP

 

[KenReay]

Hi

Not totally confused but a little!

Where is the link between the truck table and the trailer table?

From your explanation I assume you have a 'master' list of trucks and trailers and that here you are making a specific 'instance' to match an order or contract?, if that is the case I suspect you need two (or possibly three?) more tables which contain the OrderNumber (or ContractNumber, whatever) something like so:

OrderNo
TruckId
ClientId
TruckCompartmentSize
... etc

OrderNo
TrailerId
TrailerCompartmentId
TrailerCompartmentSize
... etc

I have shown two here, assuming you have more than one compartment in a trailer, which your explanation implies, so you need a table or trailer compartments containing one row per trailer per compartment, you may also need a table of trailers, containing one row per trailer, if there is data specific to only the trailer (eg trailer length or something like that), without knowing more about your datastructure I cannot say.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reay@talk21.com

 

[lomano]

Yes, i have a master table, an order_table. The user creates a new order (order number is an autonumber). it looks something like this.

order_num
truck_num
trailer_num
date
driver_name
loader_name... etc

the truck table has such data as: (trailer table is almost exactly the same)

truck_num
make_model_year
license_exp
tare_weight
max_GVW.. etc

then i have seperate compartment tables linked to the truck and trailer tables by their numbers, which has the compartment size data:

truck_num
compartment_size

so what the client wants, is when he opens the order form, he wants all the truck and trailer compartment sizes to show on the subform automatically when a truck and trailer are selected from their seperate combo boxes. so i would have to combine them into one field somehow.

 

[KenReay]

Hi

Its me again, still not clear on what you are saying, from your original post I quote:

"In addition to this, the client wants numbers beside each subform entry... 1..2..3..4 etc for each row of data to represent the compartment number."

I take this to mean you want a list of compartments, not a single field with the compartment ids concatenated into it?

If I am on the right track, do you need an Order\Compartment Table

OrderNum
CompartmentNo
CompartmentSize

You populate this with an update query (or >addNEw, .Update code in the After Update event of the truck and Trailer combo boxes, and requery the sub form.

Am I on the right track, do you need more information?

Regards




Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove upper case to use

 

[lomano]

Oh i see, there is so much more to the db that i didnt tell you, there is a product table, a driver table, location table etc. there is also an order_detail_table, that is linked by order num... looks like this:

order_num
litres
location_num
product_code
cust_order_num
etc..

I didn't put a compartment_number in the compartment tables because the user would have to type that in. The client wants to have each line in the detail to have a number beside it (on the subform of the order form, and on the report printed) to represent the compartment number of the truck (he claims his drivers will get confused). So i just need a textbox of some sort to start at 1, and add one to itself for each detail line...

 

[KenReay]

Hi

Ok, so is that it?, are you off and running now?

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use