Testing for non existing table entries

[jimleon]

The following query is supposed to bring up the electrical requirements of our customers (if any) based on their usaage last year.
It works great if that company was with us last year and has a company code in the last years electric table.

However if the customer is a new one then they wont have an entry in the table; I therefore us an iif statement to check on all fields that use the electrics last year table thus:

expr5: IIf(IsNull([last year stand no]),"x",[electrics last year].[spotlights])

which will output last years spotlight usage if that company was with us and an 'x' if it wasn't, allowing the query to finish sucessfully.

But this is not the case and I suspect my method of detecting if the company code is present in the table is faulty.

Any ideas guys??



SELECT [stands booked].[Company Code], [stands booked].[stand no], IIf(IsNull([last year stand no]),Right$([Forms]![Main Details]![Child17].[Form]![stand no],1),Right$([last year stand no],1)) AS Expr2, [show list].showcode, [show list].show, [Customer Details].CO_NAME, IIf(IsNull([last year stand no]),"x",[electrics last year].[power sockets 1]) AS expr3, IIf(IsNull([last year stand no]),"x",[electrics last year].[sunflood light]) AS expr4, IIf(IsNull([last year stand no]),"x",[electrics last year].[spotlights]) AS expr5, IIf(IsNull([last year stand no]),"x",[electrics last year].[20 amp single phase]) AS expr6, IIf(IsNull([last year stand no]),"x",[electrics last year].[30 amp three phase]) AS expr8, IIf(IsNull([last year stand no]),"x",[electrics last year].[60 amp supply]) AS expr9, IIf(IsNull([last year stand no]),"x",[electrics last year].[Special Reqs]) AS expr10, IIf(IsNull([last year stand no]),"x",[electrics last year].[srcost]) AS expr11, IIf(IsNull([last year stand no]),"x",[electrics last year].[water]) AS expr12, preferences.[daily supply cost], preferences.[sunflood light], preferences.spotlights, preferences.[20 amp single phase], preferences.[30 amp single phase], preferences.[30 amp three phase], preferences.[60 amo supply], preferences.[water cost], preferences.Year, [Year]-1 AS expr1, [stands booked].[None required]
FROM preferences, [show list], ([electrics last year] INNER JOIN [Customer Details] ON [electrics last year].SHORT_NAME = [Customer Details].SHORT_NAME) INNER JOIN [stands booked] ON [Customer Details].SHORT_NAME = [stands booked].[Company Code]
WHERE ((([stands booked].[Company Code])=[Forms]![Main Details]![Company Code]) AND (([stands booked].[stand no])=[Forms]![Main Details]![Child17].[Form]![stand no]) AND ((IIf(IsNull([last year stand no]),Right$([Forms]![Main Details]![Child17].[Form]![stand no],1),Right$([last year stand no],1)))=Right$([Forms]![Main Details]![Child17].[Form]![stand no],1)) AND (([show list].showcode)=Right$([Forms]![Main Details]![Child17].[Form]![stand no],1)) AND (([Customer Details].SHORT_NAME)=[Forms]![Main Details]![Company Code]) AND ((IIf(IsNull([last year stand no]),"x",[electrics last year].[SHORT_NAME]))=[Forms]![Main Details]![Company Code]));

[ADezii]

The following query is supposed to bring up the electrical requirements of our customers (if any) based on their usaage last year.
It works great if that company was with us last year and has a company code in the last years electric table.

However if the customer is a new one then they wont have an entry in the table; I therefore us an iif statement to check on all fields that use the electrics last year table thus:

expr5: IIf(IsNull([last year stand no]),"x",[electrics last year].[spotlights])

which will output last years spotlight usage if that company was with us and an 'x' if it wasn't, allowing the query to finish sucessfully.

But this is not the case and I suspect my method of detecting if the company code is present in the table is faulty.

Any ideas guys??



SELECT [stands booked].[Company Code], [stands booked].[stand no], IIf(IsNull([last year stand no]),Right$([Forms]![Main Details]![Child17].[Form]![stand no],1),Right$([last year stand no],1)) AS Expr2, [show list].showcode, [show list].show, [Customer Details].CO_NAME, IIf(IsNull([last year stand no]),"x",[electrics last year].[power sockets 1]) AS expr3, IIf(IsNull([last year stand no]),"x",[electrics last year].[sunflood light]) AS expr4, IIf(IsNull([last year stand no]),"x",[electrics last year].[spotlights]) AS expr5, IIf(IsNull([last year stand no]),"x",[electrics last year].[20 amp single phase]) AS expr6, IIf(IsNull([last year stand no]),"x",[electrics last year].[30 amp three phase]) AS expr8, IIf(IsNull([last year stand no]),"x",[electrics last year].[60 amp supply]) AS expr9, IIf(IsNull([last year stand no]),"x",[electrics last year].[Special Reqs]) AS expr10, IIf(IsNull([last year stand no]),"x",[electrics last year].[srcost]) AS expr11, IIf(IsNull([last year stand no]),"x",[electrics last year].[water]) AS expr12, preferences.[daily supply cost], preferences.[sunflood light], preferences.spotlights, preferences.[20 amp single phase], preferences.[30 amp single phase], preferences.[30 amp three phase], preferences.[60 amo supply], preferences.[water cost], preferences.Year, [Year]-1 AS expr1, [stands booked].[None required]
FROM preferences, [show list], ([electrics last year] INNER JOIN [Customer Details] ON [electrics last year].SHORT_NAME = [Customer Details].SHORT_NAME) INNER JOIN [stands booked] ON [Customer Details].SHORT_NAME = [stands booked].[Company Code]
WHERE ((([stands booked].[Company Code])=[Forms]![Main Details]![Company Code]) AND (([stands booked].[stand no])=[Forms]![Main Details]![Child17].[Form]![stand no]) AND ((IIf(IsNull([last year stand no]),Right$([Forms]![Main Details]![Child17].[Form]![stand no],1),Right$([last year stand no],1)))=Right$([Forms]![Main Details]![Child17].[Form]![stand no],1)) AND (([show list].showcode)=Right$([Forms]![Main Details]![Child17].[Form]![stand no],1)) AND (([Customer Details].SHORT_NAME)=[Forms]![Main Details]![Company Code]) AND ((IIf(IsNull([last year stand no]),"x",[electrics last year].[SHORT_NAME]))=[Forms]![Main Details]![Company Code]));

It seems as though you would have to do some kind of Lookup on the [electrics last year] Table, something similar to:

expr5: IIf(IsNull([last year stand no]), "x", DLookup("[spotlights]", "[electrics last year]", _
            & "[electrics last year].[last year stand no]=" & Me![last year stand no]))

[Jim Doherty]

The following query is supposed to bring up the electrical requirements of our customers (if any) based on their usaage last year.
It works great if that company was with us last year and has a company code in the last years electric table.

However if the customer is a new one then they wont have an entry in the table; I therefore us an iif statement to check on all fields that use the electrics last year table thus:

expr5: IIf(IsNull([last year stand no]),"x",[electrics last year].[spotlights])

which will output last years spotlight usage if that company was with us and an 'x' if it wasn't, allowing the query to finish sucessfully.

But this is not the case and I suspect my method of detecting if the company code is present in the table is faulty.

Any ideas guys??



SELECT [stands booked].[Company Code], [stands booked].[stand no], IIf(IsNull([last year stand no]),Right$([Forms]![Main Details]![Child17].[Form]![stand no],1),Right$([last year stand no],1)) AS Expr2, [show list].showcode, [show list].show, [Customer Details].CO_NAME, IIf(IsNull([last year stand no]),"x",[electrics last year].[power sockets 1]) AS expr3, IIf(IsNull([last year stand no]),"x",[electrics last year].[sunflood light]) AS expr4, IIf(IsNull([last year stand no]),"x",[electrics last year].[spotlights]) AS expr5, IIf(IsNull([last year stand no]),"x",[electrics last year].[20 amp single phase]) AS expr6, IIf(IsNull([last year stand no]),"x",[electrics last year].[30 amp three phase]) AS expr8, IIf(IsNull([last year stand no]),"x",[electrics last year].[60 amp supply]) AS expr9, IIf(IsNull([last year stand no]),"x",[electrics last year].[Special Reqs]) AS expr10, IIf(IsNull([last year stand no]),"x",[electrics last year].[srcost]) AS expr11, IIf(IsNull([last year stand no]),"x",[electrics last year].[water]) AS expr12, preferences.[daily supply cost], preferences.[sunflood light], preferences.spotlights, preferences.[20 amp single phase], preferences.[30 amp single phase], preferences.[30 amp three phase], preferences.[60 amo supply], preferences.[water cost], preferences.Year, [Year]-1 AS expr1, [stands booked].[None required]
FROM preferences, [show list], ([electrics last year] INNER JOIN [Customer Details] ON [electrics last year].SHORT_NAME = [Customer Details].SHORT_NAME) INNER JOIN [stands booked] ON [Customer Details].SHORT_NAME = [stands booked].[Company Code]
WHERE ((([stands booked].[Company Code])=[Forms]![Main Details]![Company Code]) AND (([stands booked].[stand no])=[Forms]![Main Details]![Child17].[Form]![stand no]) AND ((IIf(IsNull([last year stand no]),Right$([Forms]![Main Details]![Child17].[Form]![stand no],1),Right$([last year stand no],1)))=Right$([Forms]![Main Details]![Child17].[Form]![stand no],1)) AND (([show list].showcode)=Right$([Forms]![Main Details]![Child17].[Form]![stand no],1)) AND (([Customer Details].SHORT_NAME)=[Forms]![Main Details]![Company Code]) AND ((IIf(IsNull([last year stand no]),"x",[electrics last year].[SHORT_NAME]))=[Forms]![Main Details]![Company Code]));

To reconstitute tables based on that SQL is going to be a pain my end to debug in the absence of your db table fieldnames, datatypes and so on... so heres a clue for you to look at.

Check out the subtle difference in SQL language between INNER JOINS and LEFT JOINS in other words in an attempt to explain the concept in plain english for your circumstances

1) an LEFT JOIN means "Show me all Customers according to a certain criteria irrespective of whether or not they have a record in the electrics last years table"

2) an INNER JOIN is "Show me all Customers according to a certain criteria only if they have a record in the electrics last years table"

Inner joins, left joins, right joins etc can be created by you by playing with the line that joins the tables together in the query window. double click it to see what it does. It may well prompt you to have a 'rethink' on your query design.

As I see it initially you should have a serious look at LEFT joining because when structured correctly, it will display existing customers to you, even though they do not appear in last years records.

(You will know when you have a left join when you see the line change to an arrow in the query window switch to SQL view to see the resultant SQL)

I hope this makes sense to you.

Regards

Jim