Multiple filters in a single query??

[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

 

[John Vinson]

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??

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?

 

[Ernest Stokely]

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?

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

 

[John Vinson]

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

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).

 

[Ernest Stokely]

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).

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