query that duplicates records

[robert909]

Hi,

I'm using Access 2000 on Windows 2000 and hope someone can help me with the following question:

I've got 1 table with the following fields:
articlenumber
articlename
weeknumber
qty
sales

Example of first 2 records in table:
10/article A/200320/20/$2.000
20/article B/200320/15/$1.500

Suppose that there are 100 records in this table. Now I'm looking for a query that gives the following restult:
articlenumber
articlename
weeknumber
type
value

Example of first 4 records in query:
10/article A/200320/qty/20
20/article B/200320/qty/15
10/article A/200320/sales/$2.000
20/article B/200320/sales/$1.500

So this query is showing each record in the table
twice; one record per "type" (=qty or sales). The query should show 200 records in total.

Hope you can help me out with this one!
Thanks in advance for your help!

Regards,

Robert

 

[dcx693]

Robert, you can try a union query of some sort. Create the first query that returns the qty numbers, save it. Then create the query that returns the sales numbers and save it. Then combine the results using a union query.

 

[Jon K]

You can do it with a UNION query (type/paste in the SQL View of a new query, using the correct table name):-

SELECT articlenumber, articlename, weeknumber, "qty" as [Type], CStr(qty) as [Value]
FROM [TableName]
UNION
SELECT articlenumber, articlename, weeknumber, "sales", Format(sales,"Currency")
FROM [TableName]
ORDER BY [Type], articlenumber;


Note: The Value field in the query results will be converted to text as it now has to contain both numbers (qty) and currency (sales).

If you want it to be a numeric field, you can replace CStr(qty) and Format(sales,"Currency") with CSng(qty) and CSng(sales).