Complex queries - excluding records on some, but not all, of the field list 
Author Message
 Complex queries - excluding records on some, but not all, of the field list

Greetings!
   I have a need for a query that would select data combined from two
tables, based on the following criteria:

Table 1 (Proposal) contans the following fields needed for the query:

ProposalID, CustomerName

Table2 (Proposal-Products)  contains the following fields for the
query:

ProposalID (linkage to Proposal)
Sub-ID  (indicates a version of the proposal)
ProductID  (a one-to many relationship with the Proposal/Sub ID's)
ProductOption1   (a field containing information about the product
selected)
ProductOption2 (like ProductOption1)

What I need to do is to build a recordset that contains only 1 row for
each distinct ProposalID/SubID, but also gives the information for the
first record for each unique Proposal/Sub combination.

For example, given the following data in the tables:

Proposal       Sub     Product     Option1      Option2
1                     1       ProdA       100             ABC
1                     1       ProdB         50             BCD
1                     2       ProdA         75             ABD
2                     1       ProdB         80             XYZ

What I would ultimately want to see is a list as follows:

Proposal       Sub     Product     Option1      Option2
1                     1       ProdA       100             ABC
1                     2       ProdA         75             ABD
2                     1       ProdB         80             XYZ

How can I accomplish this?

Thanks



Tue, 13 Feb 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Exclude selection in datacombo based on field in record

2. New fields not listed in Field Explorer

3. The VALUE in the record field Not the Field Name

4. SQL Query - exclude parameters

5. How do I exclude a variable from a query if it is empty or null

6. Enumerate table and query names in ADO excluding system tables

7. Running a parameter query from a selected record in a list box

8. Keep track of excludes for each record

9. How do I exclude duplicate database records

10. excluding certain records at runtime

11. Excluding deleted dBASE records

12. Keep Track of Excluded Users for each Record

 

 
Powered by phpBB® Forum Software