HELP: Query Problem using IF statement

[lalaland]

I have to create a query with this:

Each painting costs $350 to ship and $500 extra if the museum purchases insurance to cover shipping. Use an IF statement to add the insurance cost.



How do I add a Total Cost Column for the query table and the calculated amount for the paintings?? Pls help

[June7]

How do you know the museum purchases insurance? Is there a field in table that indicates this? Is it a Yes/No field? Create field in query with an expression:

InsuranceCost: IIf([Insurance] = True, 500, 0)

[lalaland]

How do you know the museum purchases insurance? Is there a field in table that indicates this? Is it a Yes/No field? Create field in query with an expression:

InsuranceCost: IIf([Insurance] = True, 500, 0)

Yes. I have a Shipment Log table that indicates if the shipment is insured or not with a field: Insurance and yes it's a Yes/No field..

[June7]

Did the suggestion work?

[lalaland]

Did the suggestion work?

it did..i had to add both numbers for those who are insured (550+350) BUT it doesnt calculate the total for each shipment. For example, Shipment 1 isnt compressed to just one total, instead, there are a bunch of shipment 1s. I want to calculate the total shipment cost of each ID by not being redundant.

[June7]

Then do an aggregate (Totals) query.

Or maybe better, build a report and use Grouping & Sorting with aggregate calcs in footers. Report will allow display of detail records as well as summary calculations.

[lalaland]

Then do an aggregate (Totals) query.

Or maybe better, build a report and use Grouping & Sorting with aggregate calcs in footers. Report will allow display of detail records as well as summary calculations.

Ok thanks!

[June7]

On second thought, not sure that is correct recommendation.

Why is there a bunch of shipment 1s?

Not understanding your data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[lalaland]

On second thought, not sure that is correct recommendation.

Why is there a bunch of shipment 1s?

Not understanding your data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

Movable Art Database (2).zip

Kk I attached it.

[June7]

There is a bunch of shipment 1s because the Shipping List by ID query does not have a join clause. This causes every record in each table to associate with every record of the other table.

Your tables are not properly related.

The primary key for Paintings is PaintingID, however you are saving the OwnerID in Shipping Log. The painting is shipped, not the owner. Why aren't you saving the PaintingID pk as fk in Shipping Log?

[lalaland]

There is a bunch of shipment 1s because the Shipping List by ID query does not have a join clause. This causes every record in each table to associate with every record of the other table.

Your tables are not properly related.

The primary key for Paintings is PaintingID, however you are saving the OwnerID in Shipping Log. The painting is shipped, not the owner. Why aren't you saving the PaintingID pk as fk in Shipping Log?

Our instructor specified to add owner id in the shipping log since the instructor says that owner id is the museum? I didn't really get that part. I'm soo confused..

[lalaland]

https://www.cengagebrain.com.au/cont...1285213781.pdf I found a link online where our instructor got this project.(Moving Art Database - just scroll down the pdf) However, our deliverables are a bit different. Ours is ER diagram, Tables created in Access, Form and Subform: Shipping, Query 1: Paintings by Picasso and Warhol, Query 2:Pre-1900 and NY Paintings, Query 3: Shipping List by ID, Query 4: Shipping Costs, Reprt 1:Shipping Costs and Report 2: ERD. The Background is the same as the one given to us.

[June7]

Each owner can have multiple paintings. Which painting was shipped? You may include the ownerID (does this mean painting has a new owner and Paintings table should be edited to reflect?) but I expect paintingID is mandatory in Shipping Log. Then link the tables in the query joining on the common paintingID fields.

This assumes each shipment is for a single painting. If not, that is another issue and will need another table.

[lalaland]

Each owner can have multiple paintings. Which painting was shipped? You may include the ownerID (does this mean painting has a new owner and Paintings table should be edited to reflect?) but I expect paintingID is mandatory in Shipping Log. Then link the tables in the query joining on the common paintingID fields.

This assumes each shipment is for a single painting. If not, that is another issue and will need another table.

I got it!! Thanks soo much!!