Creating Extra Column/Row with a Query

[kshope25]

I have a table with the following info:
Supplier.......Receiver.......... Cost............... PURotue........... IntRoute.......... Del Route
ABC Inc....... Dubab ...........$44.00.............. P-355 ..............I-335 ..............D-15

I would like to know how to make a query that will add another column called "MAIN Route"

I want there to be 3 rows (since there are 3 routes listed) with each of the routes (PU Route, IntRoute and Del Route) listed as the main route as below:

Supplier..... Receiver........ Cost.......... PURoute.......... IntRoute........ Del Route...... Main Route
ABC Inc ......Dubab .........$44.00 ..........P-355 .............I-335............ D-15............ P-355
ABC Inc ......Dubab .........$44.00 ...........P-355 .............I-335........... D-15 .............I-335
ABC Inc .......Dubab........ $44.00 ..........P-335...............I-335........... D-15 ..............D-15

Is this possible?

 

[jdraw]

Suggest you review/research data base design, normalization to get proper table structure.

 

[kshope25]

Sorry, format should be readable now. Thanks. Any advice?

 

[plog]

jdraw's comment wasn't about the readability of the data you posted on this forum. It was advice about the the structure of your data in your database.

You need to normalize the structure of your tables. Based on the sample data you have provided, your data should be in 2 tables --- Deliveries and Routes. Deliveries would contain fields for DeliveryID, Supplier, Receiver and Cost. Routes would contain fields for DeliveryID, RouteType and Route.

This is how your sample data would fit into that structure:

Deliveries
DeliveryID, Supplier, Receiver, Cost
1, ABC Inc, Dubab, 44.00

Routes
DeliveryID, RouteType, Route
1, PU, P-355
1, Int, I-335
1, Del, D-15

Once that's done your query becomes easy.