Multiple filters in a single query??

  • Thread starter Thread starter Ernest Stokely
  • Start date Start date
E

Ernest Stokely

I am obviously doing something wrong. Here is my table content for a
customer order (I am paraphrasing to keep it simple):

# Part A items # Part B items # Part C items

In another small table I have:

Price part A Price part B Price part C

In writing a query to generate the total of an order I need to
calculate the sum:

# Part A items * Price part A + # Part B items* Price part B etc.

In the query two tables are used and I can filter to get the price of
the first part, no problem. When I try to add the second filter the
query fails because it is looking for a nonexistent set of prices.

How do you do this??

Ernie Stokely
 
I am obviously doing something wrong. Here is my table content for a
customer order (I am paraphrasing to keep it simple):

# Part A items # Part B items # Part C items

In another small table I have:

Price part A Price part B Price part C

In writing a query to generate the total of an order I need to
calculate the sum:

# Part A items * Price part A + # Part B items* Price part B etc.

In the query two tables are used and I can filter to get the price of
the first part, no problem. When I try to add the second filter the
query fails because it is looking for a nonexistent set of prices.

How do you do this??
Click to expand...

I'm afraid you paraphrased a bit too much.

Do you have separate FIELDS for the number of items and the prices?
Could you post the SQL view of the query that does work, and indicate
what else you want to filter?
 
John Vinson said:
I'm afraid you paraphrased a bit too much.

Do you have separate FIELDS for the number of items and the prices?
Could you post the SQL view of the query that does work, and indicate
what else you want to filter?
Click to expand...

Sorry! Yes, all are separate fields. I don't think the way I am trying
to do this is the right way to implement this common application. You
can't do what I am trying to do. Can you help straighten out my
thinking. I have stared at this so long my mind is in a rut! Thanks

Ernie
 
Sorry! Yes, all are separate fields. I don't think the way I am trying
to do this is the right way to implement this common application. You
can't do what I am trying to do. Can you help straighten out my
thinking. I have stared at this so long my mind is in a rut! Thanks
Click to expand...

Remember... "fields are expensive, records are cheap". Rather than
having a separate FIELD for each price, have a new RECORD for each
price. You're using a relational database! Use it relationally!

Since I'm not completely clear what you're trying to accomplish, I
can't be sure - but I suspect that you want one record per product,
with that product's price as a field; and another table with fields
for ProductID and quantity (and perhaps a date or other details about
that particular record).
 
John Vinson said:
Remember... "fields are expensive, records are cheap". Rather than
having a separate FIELD for each price, have a new RECORD for each
price. You're using a relational database! Use it relationally!

Since I'm not completely clear what you're trying to accomplish, I
can't be sure - but I suspect that you want one record per product,
with that product's price as a field; and another table with fields
for ProductID and quantity (and perhaps a date or other details about
that particular record).
Click to expand...


Thanks, John. I am still having trouble thinking "relationally". I now
see how to do the design. Your supposition about my intent is correct.
 
Back
Top