Append query help

[kent christensen]

So i have an append query that takes a bunch of data from different tables in the db and generates a quote in a new table called "Quote". In this Quote table there are many columns (Customer,SKU,QTY,Cost per SKU,SUm Cost,Date, Quote Id). I want to be able to generate this quote in table form for a customer, however, i want the Customer, Sum cost, Date, and Quote ID to only be listed once in this table. However, these fields get repeated for each SKU that is in each order. The customer is a text data type, the sum cost is currency data type, date is a date and time data type but it also set as a default value of Now(), and the quote Id is a autonumber data type.

I am still very new to access and dont know to much about VBA but im willing to try anthing to see if i can get a quote presentable to a customer and easily stored in a database.

[NeoPa]

Kent, I'm afraid that again you provide far too little information to be able to make sense of what you're trying to ask. remember, anything you don't tell us, we're unlikely to know or understand.

[kent christensen]

I have an append query that appends to a table called “Quote”. In this table the columns are as follows: (Customer,SKU,QTY,Cost per SKU,SUm Cost,Date, Quote Id). The customer field is a text data type, the sum cost field is currency data type, date field is a date and time data type but it also set as a default value of Now(), and the quote Id field is an autonumber data type.
When I run the query, for example the query returns 3 rows because 3 different SKU’s were found. So it would look like this:
(CSTMR) (SKU) (QTY) (cost) (Sum Cost) (Date) (Quote ID)
ABC 5GH 1 $10 $10 Sep 7 1
BCD 2I7 4 $5 $20 Sep 7 2
EFG GH9 2 $4 $8 Sep 7 3

What I want is for the Quote ID to only be listed once. The quote ID is listed as many times as there are SKU's. Since this is all on the same quote i only want a 1 instead of it being listed as 1,2,3. Is there a way to make it so this quote ID is only listed once?

Let me know if this is clear enough. I would also wish for the Date to only be listed one as well.

[NeoPa]

Kent, this is not about your query. It's about your design.

As you have it, it will not support what you want. You need to take a step back and consider what is appropriate for each of your tables (Yes. You need to store this data in separate tables. It doen't fit properly in a single table).

When you have the design sorted out you can look at queries to handle your requirements. Frankly though, when the design is right it should all be a lot easier to work with. The complication here is down to the lack of a match between the design and the data.