Complex Tables to Sum

[whitelockben]

Hi all,

I have a form which is allowing input of equipmet info. To keep is simple I have tried to dumb it down as much as possible. Here is an example of the table with some data in.



Now my aim is to be able to have a table which creates a unique record for every combination of location and seg. type.

For example from this table the new table would create a new record for ZE-A ZE-B etc etc. There will always be the same number of segrigation types however there will be an undetermined number of locations.

The aim of having these new records is to have a field in each record which sums the total weight of the equipment at each location based on the segrigation type and another for the size.

An example of what I am looking for is:





Is this possible does anyone know

[June7]

Certainly anything is possible. Just might require some very elaborate VBA coding.

What criteria would determine which equipment are summed for each SegType and Location combination?

[whitelockben]

It could be anything.

The knowns are the weight for each piece of equipment.

However there could be any combinations of seg type and equipment, more of less.

I am thinking of doing this another way and writing to a second table relating to the selection seg type, however I dont know how I would get one record for each location and keep adding the weights and sizes no new fields?

[June7]

Using the sample data, these two queries will accomplish creating the combined SegType and Location codes.

SELECT Equipment, SegType, Weight, Size, [SegType] & "-" & Left([Location],1) AS L1, [SegType] & "-" & Mid([Location],2,1) AS L2, [SegType] & "-" & Mid([Location],3,1) AS L3, [SegType] & "-" & Mid([Location],4,1) AS L4
FROM Table1;

SELECT *
FROM (SELECT Equipment, Weight, Size, L1 As Code FROM Query1
UNION SELECT Equipment, Weight, Size, L2 As Code FROM Query1
UNION SELECT Equipment, Weight, Size, L3 As Code FROM Query1
UNION SELECT Equipment, Weight, Size, L4 As Code FROM Query1) AS Q
WHERE Mid(Code,4,1)<>"";

Matching the codes with the correct weight and size calcs requires reliable criteria. Is code ZE-A always EQUIP1 + EQUIP5? If there is no consistent pattern, I don't see this can be done.