2nd Posting - complex nested query

[Julia Boswell]

Haven't had an answer to this one, so I'll try again, I really need some
pointers:

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

 

[David Cox]

It looks to me like you are confusing yourself by thinking list of part
numbers, list of lifecycles.

You seem to be thinking report, not query.

A query can easily give you:

QuoteNo, RaisedBy, Customer, PartNo, Lifecycle, Value

A report (which you can preview on the screen) based on that query can
easily give you:

Quote number, Raised by, Customer, Total Value
Part No, Lifecycle, value
Part No, Lifecycle, value
......

Quote number, Raised by, Customer, Total Value
Part No, Lifecycle, value
Part No, Lifecycle, value
......
Does this help?

 

[Julia Boswell]

Thanks for the response. Unfortunately it doesn't help, I understand reports
and could easily do it that way. However I can't use a report. I'm querying
an Access database through a .NET application and somehow need to display
the records using SQL.

Julia

 

[Steve]

You could do this through a union of three queries. The first would give you
QuoteNo, RaisedBy and Customer. The second would give you a lsit of PartNos
and Lifecycles. The third would give you TotalValue. The result would appear
with the firstline showing QuoteNo, RaisedBy and Customer. That would be
followed by the listing of parts and lifecycles. The last line would show
TotalValue.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Julia Boswell]

Thanks Steve

I'll give that a go.

Julia

You could do this through a union of three queries. The first would give
you QuoteNo, RaisedBy and Customer. The second would give you a lsit of
PartNos and Lifecycles. The third would give you TotalValue. The result
would appear with the firstline showing QuoteNo, RaisedBy and Customer.
That would be followed by the listing of parts and lifecycles. The last
line would show TotalValue.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Steve]

In case you get stuck, all three queries need the same number of fields. You
accomplish this by adding fields to your query that look like:
Dummy1 = Null
Dummy2 = Null
etc


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)