Little Advice About A series of Queries I need to Build

[NYFashionToGo]

Hi and Thaks for looking at this post. I could use a little advice about a query I need to build. Just trying to get it straight in my head before I start.

This query is to specifically build USPS International Shipping labels. Which I pull out of access into excel and Build an XML feed.

My problem is I have to write descriptions for each product I have shipped. The way Endicia Shipping works is I have to assign tags for each description.
I Have CustomsQty1, CustomsValue1,CustomsDescription1, For the second product I have CustomsQty2, Customs Value2,CustomsDescription2 and so On....
Now here is the problem. If all the fields Labeled 1 are null It gives me issues with physically printing the labels. So if it is null I need to choose product 2 and put it inside of product one fields. If Product 1 & 2 is Null Grab it from Product 3 and Put it inside product1 then Nulling out product 3.

What is the best way to achieve a query like this? I feel somewhat Limited in my queries thinking I am missing something. So I thought I would ask the board for some advice.
Thanks

 

[lespaul]

does your table have fields
CustomsQty1, CustomsValue1,CustomsDescription1
CustomsQty2, CustomsValue2,CustomsDescription2
CustomsQty3, CustomsValue3,CustomsDescription3

what if someone orders 10 items?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[NYFashionToGo]

Well Each product is Generaly Summarized. It would eventually say on the shipping Label, 5 Widgets A- Value 75.99


Yes I have the fields set up. Just trying to bounce it over if the first one is null. I realize it will take a series of queries to get a final result..... I dont need bounce all of them, I just need to bounce one of them Into the #1 field...

 

[lespaul]

Ok, again, are the fields in your table:

CustomsQty1 CustomsValue1 CustomsDescription1 CustomsQty2 CustomsValue2 CustomsDescription2 CustomsQty3 CustomsValue3 CustomsDescription3

or do you have a normalized table?

What's the SQL of one of the queries?

 

[NYFashionToGo]

Yes That is exactly what I have. Exactly (Right now Up to 5). It was little difficult to do because I need to summarize a description and value weight and quantity per type of product.

 

[lespaul]

ok then first create a query that normalizes your data:

SELECT CustomsQty1, CustomsValue1, CustomsDescription1, "First Order" FROM TableName
SELECT CustomsQty2, CustomsValue2, CustomsDescription2, "Second Order" From TableName
etc..

then use this query as your source.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins