Help with complex query

[Julia Boswell]

Hi all,

I've got a query that I'm not sure how to develop.

My tables:

Quotes - QuoteNo, RaisedBy, Customer
QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value

There's a one-to-many relationship between Quotes and QuoteItems, i.e. one
quote can have many items.

I need to run a query to show a list of quotes with totals from the
QuoteItems table i.e.

QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue

I haven't got a clue how to start this, I know it needs to be nested
queries, but the listing of parts and lifecycles is particularly stumping
me.

Any ideas?

Julia

 

[Guest]

Create a normal query with the two tables joined in the QBE grid. Bring down
any fields needed first from the Quotes table then the other.

While in design view, go up to View and select Totals.

That will add Group By below each field. Leave it alone for the Quotes
table. For the QuoteItems table change Group By to either Sum or Count. Run
the query and see what happens. Go back to design view and adjust as
necessary.