Merging data within a record

[cruiser01]

I have an Access table that maintains a history of our shipments. Within each record (shipment)there 6 fields that may or may not be used to reference orders filled on that shipment. All of the data is currently being pulled into a query to do some calculations for doing analysis various cost attributes from the main table. I would like to be able to create a query from data pulled with the main query will simply return list all of the various reference numbers and a particular common cost element connected to that order number. I have tried various methods to do this including a union query, but don't get the right results I am looking for. I am pretty much a novice, so need some help. I know this is probably too vague to get very far, but wanted to start up a line of communication.

 

[Frink]

Hallo,
If the 6 reference fields are all holding the same type of data, then it would probably be better to store them in a separate table, holding ShipmentId (the primary key of the shipment table) and a single reference field. Link the tables on Shipment Id (1-Shipments to many-references) and the query will be simple.
Alternatively, you could use a Union query:

SELECT ShipmentId,Reference1 AS Reference WHERE len('' & Reference1)>0
UNION
SELECT ShipmentId,Reference2 WHERE len('' & Reference2)>0
UNION
SELECT ShipmentId,Reference3 WHERE len('' & Reference3)>0
UNION
SELECT ShipmentId,Reference4 WHERE len('' & Reference4)>0
UNION
SELECT ShipmentId,Reference5 WHERE len('' & Reference5)>0
UNION
SELECT ShipmentId,Reference6 WHERE len('' & Reference6)>0

You'll have to open a new query with no tables, select SQL view and paste the above in.

- Frink

 

[cruiser01]

Let me give you a little more detail. Per say shipment has 5 orders own it. The 1st order on the shipment becomes the master ID (simply for reference on a master BOL) and there are 4 other orders. One piece of information that is common to the whole shipment is the total freight cost (we only ship orders as truckloads). One element I calculate is freight cost per box, which is common to the whole shipment and would apply to any order on that shipment regardless of where it ships to. I have been asked to query out a simple flat file that would list each order with the cost per box so that data can be pulled into another SQL database in a custom application that processes orders so delivered cost to a customer can be captured. I wrote a union query but get duplication. If there are 5 orders, it returns 25 records, etc. Obviously, I am not a programmer, but can remember times in the past working with programmers as a project manager in various database environments (most recently using Java with an Oracle database) where they had some "misfires" that gave this type of result, just couldn't remember the reason or the fix in the relationship within the query.

 

[tracy1234]

Cruiser01-

You should probably post your table structure(s) with some sample data. It is not clear from your last post if you are using your denormalized shipments table or have separated out your orders to another table.

Also, if you could specify how you are calculating the per box cost, that would help. If it is a query, you may want to post that query too.

Helpful too would be to post the query that produces the 25 records where only 5 are desired. This kind of "misfire" can usually be corrected by proper joins and group bys.

-Tracy

 

[cruiser01]

Ok, in one query called COSTDATA I return the following based on table where the data is entered:

DATE
MASTER ORDER (Alias for Order 1)
ORDER 2
ORDER 3
ORDER 4
ORDER 5
ORDER 6
TOTAL WT
FREIGHT COST
COST PER LB (caculated field within the query-FREIGHT COST/TOTAL WT)

The flat file I need would simply be to bring back each individual order number with the cost per lb. that was calculated and tied within the record where it resides. record. So the end result would just two fields that I will call "ORDER NO" and then a "COST PER LB".