SQL query over mutiple tables

[Heidestrand]

Hello community,

I have a problem / question regarding a SQL query and I hope you can help me. My initial situation:

I have 3 tables that I INNER JOIN together with a number. The number is the same in each table. By doing this I get a big table with 11 columns. The tables I use look like this:

table 1: tblDetector
DetectorIDRef | SAPNr | Detector

table 2: tblEKLand
EKLandIDRef| SAPNr | EKLand

table 3: tblSysSold
ID | SAPNr | SystemSold | CountryID | DetectorID

What I want is: I want to count all the SAPNr numbers when I apply certain criteria. An exmaple:

- How many SAPNr numbers do I have when tblDetector.Detector = X AND tblEKLand.EKLand = China AND tblSysSold.SystemSold = biplane?

For this I wrote a SQL query:

SELECT Count(*) AS SAPNr " & _
           "FROM (tblSysSold INNER JOIN tblEKLand ON tblSysSold.SAPNr = tblEKLand.SAPNr) INNER JOIN tblDetector ON tblEKLand.SAPNr = tblDetector.SAPNr " & _
           "WHERE tblDetector.Detector ='" & strDetec & "' AND tblEKLand.EKLand ='" & strLand & "' AND tblSysSold.SystemSold ='" & strSys & "'"

Now my problem occurs: my SQL query only works when all my criteria are selected. But what is if I only have 2 criteria selected? Let's say I only want to know how many systems were sold in China? Or only how many detectors of a special kind are sold overall?

Then my code gives me 0 because the other two criteria are missing.

Therefore my question is: Is there a possibility how I can alter my code that a NULL value is also possible? So that my code also works when I didn't select all criteria?

Hope you understand my problem and can help me.


Best regards,

Heide

 

[namliam]

Best way to solve this is by "fixing" the sql in VBA prior to executing it.

Something like

Dim MySQL as string
mySQL = " SELECT Count(*) AS SAPNr " & _
           " FROM (tblSysSold INNER JOIN tblEKLand ON tblSysSold.SAPNr = tblEKLand.SAPNr) INNER JOIN tblDetector ON tblEKLand.SAPNr = tblDetector.SAPNr " & _
           " WHERE 1=1 "
If strdetec <> "" then 
   mysql = mysql & " AND tblDetector.Detector ='" & strDetec & "'"
Endif

etc...

Good luck

 

[vbaInet]

You will build your code accordingly. Perform a check to see if the textbox has a value or not, if it does build the WHERE part of your SQL, otherwise skip it and move to the next textbox validation.

Why the multiple one-to-one relationships anyway? Are you aware of normalisation rules?

Edit: Bit late aren't I

 

[Heidestrand]

@namliam:

Thank you for your code, this is exactly what I was looking for =))
I'm still new into VBA and Access and I didn't conceive of if strdetec <> "" then and then attaching the right "tail" to the SQL string. Didn't know it could work like this.
Learning never stops

@vbaInet:
I'm aware normalization rules, I already connected the tables like this:

But.. I mean I've to be honest.. I don't really know what does this do for me. I know that I can edit the information inside the tables that are connected now. But I built some formulas and used VBA with SQL strings to write, delete and alter the information inside the tables in general.

Would you be kind enough to tell me how you would do it? Instead of the multiple one-to-one relationships you mentioned?

 

[namliam]

Multiple tables with a 1:1 relationship generaly just belong in 1 table.

In this case though I dont see why SAPNr would belong in all 3 tables?
Per your design view you shouldnt be joining on SAPNr but rather on CountryID and DetectorID.

More over this shouldnt be a 1:1 relationship, rather a N:1 atleast I hope you are not limited to selling any system only once in any country....

 

[CJ_London]

I presume your strings are coming from a form and the user does not necessarily complete all of them

 mySQL="....WHERE (" & strDetec & " is null or tblDetector.Detector ='" & strDetec & "') AND (" & strLand & " is null OR tblEKLand.EKLand ='" & strLand & "') AND ....

or

 mySQL="....WHERE "
 if not isnull(strDetec) then MySQL=MySQL & "tblDetector.Detector ='" & strDetec & "' AND "
 if not isnull(strLand) then MySQL=MySQL & "tblEKLand.EKLand ='" & strLand & "' AND "
...
 ...
 MySQL=left(MySQL, len(MySQL)-5) ' to remove the last ' AND '

 

[vbaInet]

Would you be kind enough to tell me how you would do it? Instead of the multiple one-to-one relationships you mentioned?

Multiple EKLands and multiple Detectors.

tblSysSold	
----------
ID
SAPNr (PK)
SystemSold
EKLandID - fk to tblEKLand
DetectorID - fk to tblDetector


tblEKLand
---------
EKLandID 
EKLand


tblDetector
-----------
DetectorID
Detector

 

[Heidestrand]

Sorry for my late answer. Only wanted to thank you all again for your help
and @vbaInet: I made the connections like you said. Works fine so far

 

[vbaInet]

Glad you got it working!