473,573 Members | 2,520 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Testing for non existing table entries

jimleon
74 New Member
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.spo tlights, preferences.[20 amp single phase], preferences.[30 amp single phase], preferences.[30 amp three phase], preferences.[60 amo supply], preferences.[water cost], preferences.Yea r, [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)=Righ t$([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]));
Oct 9 '07 #1
2 1166
ADezii
8,834 Recognized Expert Expert
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.spo tlights, preferences.[20 amp single phase], preferences.[30 amp single phase], preferences.[30 amp three phase], preferences.[60 amo supply], preferences.[water cost], preferences.Yea r, [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)=Righ t$([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:
Expand|Select|Wrap|Line Numbers
  1. expr5: IIf(IsNull([last year stand no]), "x", DLookup("[spotlights]", "[electrics last year]", _
  2.             & "[electrics last year].[last year stand no]=" & Me![last year stand no]))
Oct 9 '07 #2
Jim Doherty
897 Recognized Expert Contributor
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.spo tlights, preferences.[20 amp single phase], preferences.[30 amp single phase], preferences.[30 amp three phase], preferences.[60 amo supply], preferences.[water cost], preferences.Yea r, [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)=Righ t$([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
Oct 9 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

5
1692
by: Arvin Portlock | last post by:
I can't come up with a query that works. Can anyone help? Conceptually the relationships are easy to describe. I have a table for books (Entries), a table for authors (Authors), and a linking table between the two because books often have more than one author (OA_Link). This situation is simple and common. A query to list each title and all...
3
3201
by: Ole Hanson | last post by:
Hi I am trying to engineer a way of testing that my logging framework is capable of writing to my eventlog. I want to include this test in my already existing NUnit tests - but I'm a little low on ideas as how to assert that I actually did write to the eventlog successfully. Naturally I want this assertion without having to open my...
1
3423
by: Nils Rennebarth | last post by:
I have a table that is essentially a log where new entries are streaming in continually and from time to time I throw old entries away to keep the table from growing. I understand that in addition to issue a DELETE FROM log WHERE date < xxx I also need to issue a VACUUM log so that new entries will use the space of deleted entries. Now...
13
4164
by: nyt | last post by:
I have a problem of number and text field. I got the database file(mdb) that contains many combo boxes used and its list values are created by "value list" For eg field Field name= 'furniture' , data type='Number' ,Display Control='Combo Box', RowSource Type = 'Value List' and Row Source = ' 0;"chair";1;"Table";2;"Bed" ' Therefore, in...
2
2055
by: chuy08 | last post by:
Basically I am using PHP 5.1.2 with Apache 2.0.5 on a FreeBSD 5.4 box with Mysql 4.1.1 running. I am attempting to write information to a Mysql table called Jabber. I can connect successfully, query data, and write data pretty much without problem. My problem is this. I am trying to do some duplicate checking on this table so I query...
1
1297
by: exapplerep | last post by:
I have a table with a particular field storing the names of authors. When I enter a new record I'd like to have a list of the existing entries come up from which one may choose. It is also important that the user be able to enter a new author's name. Any list I've been able to generate so far lists the contents of that field but shows every...
2
2045
by: DavidPr | last post by:
I'm creating (trying to create) a picture gallery for my website. The script is not working. I've been working on it now for about 80 hours with no success. My php skills aren't very good. This picture gallery uses 2 mysql tables: acwrtcats and acwrtpics The table acwrtcats has 2 fields: cat_name and cat_desc. The cat_name is the name of...
14
10844
by: deejow | last post by:
U guys/gals are awesome! I have a "Claimant Master" table with a primary key called "Claimant ID" set to number data type. It has existing records linked to an enormous "Jobs" table. There are thousands of existing entries and some Claimants have several entries. I want to leave the existing entries untouched and get the database to fill in...
10
4095
by: hedges98 | last post by:
I'm not sure if the title of the thread is relevant but I think it explains my problem... This is going to be loooooong... Basically, I am working on altering/improving an existing database but have been asked to change something which I think requires a whole restructuring of the database (to be honest, the relationships and tables seem a bit...
0
7989
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7744
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8039
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6369
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5559
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5271
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3708
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1271
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1008
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.