Help with complex query

J

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
 
J

Jerry Whittle

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top