How To Query - Separating Header Info and Line Item info. 1

[dominicdunmow]

Hi there,

I have a table which I've simpified below. I'm trying to build a query where the dataset returns a row consisting of the "header field" details, then the following lines return the "line item" details.

(Header field) (line item) (line item) (line item)

Customer Project code Service Revenue
--------- ------------ ------- -------
Joe 1111 Engineer 50
Joe 2222 Spare Part 100
Joe 3333 Engineer 110

In essence I would like a query from the above info. to be structured something like:-

Customer Project code Service Revenue
--------- ------------ ------- -------
Joe
1111 Engineer 50
2222 Spare Part 100
3333 Engineer 110


Can anyone tell me if this is possible, I can do it as a report, but these are too unwieldy for what I want them for.

Thanks

 

[dhookom]

Try a union query:
SELECT DISTINCT Customer, Null As ProjectCode,
Null As Service, Null As Revenue
FROM tblA
UNION ALL
SELECT Null, [Project Code], Service, Revenue
FROM tblA;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]