Need to set up multiple queries and forms 2

[memkam]

The scenario is: I have 3 tables.

Commercial: NewPrice, OldPrice, Weight, Zone
Residential: NewPrice, OldPrice, Weight, Zone
TransactionReport: Price, Weight, Zone, IsItRes (a boolean)

Task at hand: Write a query, which takes all the records in the transaction table, first of all checks whether it is a residential or Commercial by looking at IsItRes field in the TransactionReport table.

If a specific record is a residential delivery, then matches the service type, weight and zone in the TransactionReport table to the Residential table and displays the Weight, Zone, Price, NewPrice from Residential table and OldPrice from Residetial table.

If record is not residential, then prints similar information from the commercial table.

I have attched my SQL code, any help or suggestions to improve logic will be greatly appreciated.

SELECT IIf ((TransactionReport.IsItRes) = TRUE),
(TransactionReport.Zone, TransactionReport.Weight, TransactionReport.Price, ResPrice.ContractPrice, ResPrice.HistoricPrice), (TransactionReport.Zone, TransactionReport.Weight, TransactionReport.Price, ComPrice.ContractPrice, ComPrice.HistoricPrice)
FROM TransactionReport, ResPrice, ComPrice
Where (((TransactionReport.Zone = ResPrice.Zone) & (TransactionReport.weight = ResPrice.Weight)) OR ((TransactionReport.Zone = ComPrice.Zone) & (TransactionReport.weight = ComPrice.Weight)))

Thanks,

kam

 

[lespaul]

If you type Nz in the Help, you'll find out exactly what PHV was suggesting. Nz allows you to enter one thing if the field is NULL and the field information if it's not NULL.

While you are correct, it would be easier for you if someone just told you what to do, but that doesn't help you learn how to find things out on your own. We are all here donating our time to helping others, that doesn't mean we should give you ALL the answers.

Leslie

 

[memkam]

Yea you are right. PHV, I apologize. Consider it to be frustration on my part. Quit smoking yesterday, usually I am not so edgy.

Hopefully you can understand.

- Maria

 

[lespaul]

we can all understand!!!

Did you get your query working correctly?

leslie

 

[memkam]

No, Not Yet.

 

[lespaul]

Did you search the forum and the help for the NZ function? You should be able to find many examples of how it works. I don't do much Access programming, but I think it's something like:

Nz(FieldName, value if null)

and you just use it in the query.

HTH

Leslie