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

 

[PHV]

You may consider an Union query of 2 select: one for residential, the other for commercial.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[memkam]

I have made some progress with the query. I am able to match all the transactions with the same zone from the transactions table to the residential table.

However, the query for some reason does not traverse to the next zone.

Also, wanted to know if there is a way for me to inser the if functionality. That is.. if ItIsRes = true then matc with res, otherwise match with com.?

Added another field, service type but you can ignore it or consider it.

again.. new code is attached

SELECT TransactionReport.ServiceType, TransactionReport.Weight, TransactionReport.Zone, TransactionReport.Price, ResPrice.Contractprice, ResPrice.HistoricPrice, ResPrice.ServiceType, ResPrice.Weight, ResPrice.Zone
FROM TransactionReport, ResPrice
WHERE (((ResPrice.ServiceType)=[TransactionReport].[ServiceType]) AND ((ResPrice.Weight)=[TransactionReport].[Weight]) AND ((ResPrice.Zone)=[TransactionReport].[Zone]) AND ((([TransactionReport].[ServiceType]=[ResPrice].[ServiceType]) & ([TransactionReport].[Zone]=[ResPrice].[Zone]) & ([TransactionReport].[Weight]=[ResPrice].[Weight]))<>False));

 

[PHV]

Be aware that & is the concatenation operator, not the logical AND.
Have you tried the union query ?
If not, I guess you have to take a look at outer joins.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[memkam]

PHV,

I understand that you are a whiz at ACCESS & SQL. I have been stuck on this for the past 3 days. Please assist me with my sql code. I am getting a syntax error.

Select IIF ((TransactionReport.IsItRes = TRUE),

(TransactionReport.Zone , TransactionReport.Weight, TransactionReport.ServiceType, TransactionReport.Price, ResPrice.Weight, ResPrice.Zone, ResPrice.Contractprice, ResPrice.HistoricPrice FROM TransactionReport, ResTable WHere TransactionReport.Zone = ResPrice.Zone AND TransactionReport.Weight = ResPrice.Weight AND TransactionReport. ServiceType = ResPrice.ServiceType),

(TransactionReport.Zone, TransactionReport.Weight, TransactionReport.ServiceType, TransactionReport.Price, ComPrice.Weight, ComPrice.Zone, ComPrice.Contractprice, ComPrice.HistoricPrice FROM TransactionReport, ComTable WHere TransactionReport.Zone = ComPrice.Zone AND TransactionReport.Weight = ComPrice.Weight AND TransactionReport. ServiceType = ComPrice.ServiceType
))

Thanks,

Maria

 

[PHV]

Try something like this:
SELECT T.Zone, T.Weight, T.ServiceType, T.Price, R.Weight, R.Zone, R.Contractprice, R.HistoricPrice
FROM TransactionReport T, ResPrice R
WHERE T.Zone = R.Zone AND T.Weight = R.Weight AND T.ServiceType = R.ServiceType
AND T.IsItRes
UNION
SELECT T.Zone, T.Weight, T.ServiceType, T.Price, C.Weight, C.Zone, C.Contractprice, C.HistoricPrice
FROM TransactionReport T, ComPrice C
WHERE T.Zone = C.Zone AND T.Weight = C.Weight AND T.ServiceType = C.ServiceType
AND NOT T.IsItRes
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[memkam]

PHV, you leave me speechless. UNION, a simple UNION... funny thing is you have been asking me to do this for the past 3 days and I had no idea how to do it. I am spending this weekend reading access.

Worked like a charm, however, need to add more functionality to the same statement.

Basically, if looking in Res table or Com table depending on the IsItRes and if it doesn't find an occurence then it should print "Occurence not find" and also list the record details from Transaction report so we can notate an exception for the accounts that do not match.

Thanks again!

Maria

 

[PHV]

So, again, take a look at outer joins.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[memkam]

Can you recommend a good site where I can read about joins, and understand all they do?

 

[PHV]

The F1 key is a good tool.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[memkam]

PHV,

I looked at the joins on Access Help. I can create that in a serperate query, but how do I insert that functionality to the existing SQL statement. I would appreciate it if you can gimme some pointers there, preferably an example.


The way I look at it,

Select X, Y from table X and table Y where tableX.X = tableY.Y. If tableX.X is not equal to tableY.Y, print X from table X and "Occurence not found"

How I do this in SQL is another story. Again, appreciate you looking into this!


Thanks,

-M

 

[PHV]

The basic idea is a LEFT JOIN and either an IIF or Nz function call.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Check out 'Understanding SQL Joins'

leslie

 

[memkam]

lespaul,

thanks for the site, gave me some idea of how joins work.

- Maria

 

[memkam]

This is what I got. Still not able to print a NULL or a message when entries don't match.

Also for some reason, duplicate entries are not printed. I would like for them to be printed out.

SELECT T.Zone, T.Weight, T.ServiceType, T.Price, R.Zone, R.Weight, R.Contractprice, R.HistoricPrice
FROM TransactionReport T Left Join ResPrice R ON T.Zone = R.Zone AND T.Weight = R.Weight AND T.ServiceType = R.ServiceType
WHERE T.Zone = R.Zone AND T.Weight = R.Weight AND T.ServiceType = R.ServiceType
AND T.IsItRes
UNION SELECT T.Zone, T.Weight, T.ServiceType, T.Price, C.Zone, C.Weight, C.Contractprice, C.HistoricPrice
FROM TransactionReport T Left Join ComPrice C ON T.Zone = C.Zone AND T.Weight = C.Weight AND T.ServiceType = C.ServiceType
WHERE T.Zone = C.Zone AND T.Weight = C.Weight AND T.ServiceType = C.ServiceType
AND NOT T.IsItRes;

Thnks again!!

I really appreciate this.

- Maria

 

[memkam]

Has everyone gone home? I need help with the above. Don't mean to be whining but I will be at work till I complete this. Help!!! Its friday!!

 

[memkam]

I am now able to incorporate entries that don't match, however still struggling with duplicates and printing a message to indicate that a NULL value exists.

SELECT T.Zone, T.Weight, T.ServiceType, T.Price, R.Weight, R.Zone, R.Contractprice, R.HistoricPrice
FROM TransactionReport T, ResPrice R
WHERE T.Zone = R.Zone AND T.Weight = R.Weight AND T.ServiceType = R.ServiceType
AND T.IsItRes
UNION SELECT T.Zone, T.Weight, T.ServiceType, T.Price, C.Weight, C.Zone, C.Contractprice, C.HistoricPrice
FROM TransactionReport T, ComPrice C
WHERE T.Zone = C.Zone AND T.Weight = C.Weight AND T.ServiceType = C.ServiceType
AND NOT T.IsItRes;

 

[memkam]

I apologize for asking and answering my own questions.. but I am ecstatic:

SELECT T.Zone, T.Weight, T.ServiceType, T.Price, R.Weight, R.Zone, R.Contractprice, R.HistoricPrice
FROM TransactionReport T LEFT JOIN ResPrice R ON T.Zone = R.Zone AND T.Weight = R.Weight AND T.ServiceType = R.ServiceType
WHERE T.IsItRes
UNION ALL SELECT T.Zone, T.Weight, T.ServiceType, T.Price, C.Weight, C.Zone, C.Contractprice, C.HistoricPrice
FROM TransactionReport T LEFT JOIN ComPrice C ON T.Zone = C.Zone AND T.Weight = C.Weight AND T.ServiceType = C.ServiceType
WHERE NOT T.IsItRes;

Got all the entries, duplicates, unmatched etc etc.

Just need to know how to print an error message... in the records that don't match?

 

[PHV]

Just need to know how to print an error message... in the records that don't match
Play with the Nz function on the R.* and C.* columns.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[memkam]

If you know what the statement should read like, wouldn't it be a lot easier for both of us if you entered the format or what the code should be rather than say "play with the following statements". Thanks for the pointer but it doesn't help too much.

- Maria