473,738 Members | 2,645 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 1173
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
1701
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 the authors associated with that title looks like this: SELECT Entries.TitleStatement,...
3
3214
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 eventlog and visually verify the writing.
1
3433
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 I want to reserve a certain amount of disk storage to hold the log table. So I first let the table...
13
4180
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 data sheet view of table, if we select (1 : Table ) ,
2
2064
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 the dbase with the following:
1
1306
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 record rather than just the unique names. In other words: If "Smith" appears in that field for 12 of...
2
2059
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 a category that pictures will be filed under, and cat_desc is a brief description of the category. ...
14
10856
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 the Claimant ID automatically from now on. Can this be done? It won't let me change it to autonumber...
10
4106
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 weird/needless to me). I've never tried altering the structure of a database before so have a whole...
0
8787
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9473
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9334
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9259
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8208
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6750
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4569
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4824
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3279
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.