MultiTable Query

[Peter Carlson]

I've been baning my head with this query.

We have a table with the following fields:
Table:Incident
id: autonumber id
person: reference to Person.ID
role: reference to Roles.ID
action1: reference to Actions.Code
action2: reference to Actions.Code
action3: reference to Actions.Code
action4: reference to Actions.Code
.... many more fields not pertinent to the question

Table: Person
id, last, first, ...
3, Carlson, Peter

Table: Roles
code, descr
1, Major Role
2, Minor Role
....

Table: Actions
code, descr (code is 2 char)
1, Did something
2, Did something else
NN, Did nothing

So assuming the following entry in the Incident table:
1, 3, 2, 1, NN, , ,
when I retrieve the sql (for html report) I want the following
id, fullname (Last + First), Descriptive Role, Desriptive Action1-4
separated with <br> if they exist
1, "Carlson, Peter", Minor Role, Did Something<br>Did nothing

So far we are using the following SQL which pulls most everything together:
SELECT IncidentNumber, FORMAT(I.Arrived, 'hh:nn') as Arrived,
FORMAT(I.Cleared, 'hh:nn') as Cleared, A.name AS apparatus, r.descr AS role,
B.descr AS Actions1, I.Actions2, I.Actions3, I.Actions4, (Actions1) &
('<br>'+Actions2) & ('<br>'+Actions3) & ('<br>'+Actions4) as Actions FROM
Incidents_Apparatus AS I, Apparatus AS A, tApparatus_Use AS R,
tIncident_Actions AS B WHERE I.IncidentNumber='%id%' and I.Vehicle=A.id and
I.Role=R.code and I.Actions1=B.code and I.Actions2=B.code and
I.Actions3=B.code and I.Actions4=B.code;

However we have 2 problems:
1. the concat of Actions 1-4 always adds the <br> even if the field is
empty (doesn't appear to be null, but appears as LEN(0) ).
2. if Actions1-4 is empty, then it is not able to find an appropriate entry
in the Actions Table (because there is no empty action)

I could add an "empty" row to the Actions table because the code column is a
2 char field, however if the code column was an autonumber ID like it is in
most of our other tables then I couldn't add an empty field. I could also
spatter my SQL with IIF to test the LEN of Actions1-4 and then concat
together, but that's not ANSI sql and wont work outside of MS. Although I
already need to maintain 2 tables of SQL, our product on the "heavy" end
supports oracle and mySql. The lite version uses Jet to an access database.
access doesn't use DATE_FORMAT and mySQL doesn't use FORMAT.

Is there any way to accomplish this?

Thanks, and sorry for the lengthy post.
Peter

 

[Alick [MSFT]]

Hi Peter,

If I understand the issue correctly, it seems you query has hard coded
<br>, so <br> will be added even if the fields (actions1 to actions4) are
empty/blank;

(Actions1) & ('<br>'+Actions2) & ('<br>'+Actions3) & ('<br>'+Actions4) as
Actions

Please correct me if I misunderstood it.



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.


--------------------
| From: "Peter Carlson" <[email protected]>
| Subject: MultiTable Query
|
| I've been baning my head with this query.
|
| We have a table with the following fields:
| Table:Incident
| id: autonumber id
| person: reference to Person.ID
| role: reference to Roles.ID
| action1: reference to Actions.Code
| action2: reference to Actions.Code
| action3: reference to Actions.Code
| action4: reference to Actions.Code
| ... many more fields not pertinent to the question
|
| Table: Person
| id, last, first, ...
| 3, Carlson, Peter
|
| Table: Roles
| code, descr
| 1, Major Role
| 2, Minor Role
| ...
|
| Table: Actions
| code, descr (code is 2 char)
| 1, Did something
| 2, Did something else
| NN, Did nothing
|
| So assuming the following entry in the Incident table:
| 1, 3, 2, 1, NN, , ,
| when I retrieve the sql (for html report) I want the following
| id, fullname (Last + First), Descriptive Role, Desriptive Action1-4
| separated with <br> if they exist
| 1, "Carlson, Peter", Minor Role, Did Something<br>Did nothing
|
| So far we are using the following SQL which pulls most everything
together:
| SELECT IncidentNumber, FORMAT(I.Arrived, 'hh:nn') as Arrived,
| FORMAT(I.Cleared, 'hh:nn') as Cleared, A.name AS apparatus, r.descr AS
role,
| B.descr AS Actions1, I.Actions2, I.Actions3, I.Actions4, (Actions1) &
| ('<br>'+Actions2) & ('<br>'+Actions3) & ('<br>'+Actions4) as Actions FROM
| Incidents_Apparatus AS I, Apparatus AS A, tApparatus_Use AS R,
| tIncident_Actions AS B WHERE I.IncidentNumber='%id%' and I.Vehicle=A.id
and
| I.Role=R.code and I.Actions1=B.code and I.Actions2=B.code and
| I.Actions3=B.code and I.Actions4=B.code;
|
| However we have 2 problems:
| 1. the concat of Actions 1-4 always adds the <br> even if the field is
| empty (doesn't appear to be null, but appears as LEN(0) ).
| 2. if Actions1-4 is empty, then it is not able to find an appropriate
entry
| in the Actions Table (because there is no empty action)
|
| I could add an "empty" row to the Actions table because the code column
is a
| 2 char field, however if the code column was an autonumber ID like it is
in
| most of our other tables then I couldn't add an empty field. I could also
| spatter my SQL with IIF to test the LEN of Actions1-4 and then concat
| together, but that's not ANSI sql and wont work outside of MS. Although I
| already need to maintain 2 tables of SQL, our product on the "heavy" end
| supports oracle and mySql. The lite version uses Jet to an access
database.
| access doesn't use DATE_FORMAT and mySQL doesn't use FORMAT.
|
| Is there any way to accomplish this?
|
| Thanks, and sorry for the lengthy post.
| Peter
|
|
|

 

[Peter Carlson]

No, the issue stems from the fact that Access doesn't treat a 0 length
string as null - that's prettry weird. So I can fix the "br" problem with
an IIF statement. - That's the first problem. I dont mind using an IIF
statement, except that it's not ANSI SQL.

The second problem is much more troublesome (as shown below). If the field
is empty (0 length) then the where clause causes the select to retrieve 0
rows since there is no record where actions2=tIncidents_Actions.Code

Peter

 

[Alick [MSFT]]

Hi Peter,

How about enable/disable AllowZeroLength property? I will do some test to
see if there is any resolution.




Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
From: "Peter Carlson" <[email protected]>
References: <e#JHzn#[email protected]> <1MCGK9GbDHA.2140
X-Tomcat-NG: microsoft.public.access.queries

No, the issue stems from the fact that Access doesn't treat a 0 length
string as null - that's prettry weird. So I can fix the "br" problem with
an IIF statement. - That's the first problem. I dont mind using an IIF
statement, except that it's not ANSI SQL.

The second problem is much more troublesome (as shown below). If the field
is empty (0 length) then the where clause causes the select to retrieve 0
rows since there is no record where actions2=tIncidents_Actions.Code

Peter

 

[Alick [MSFT]]

Hi Peter,

A zero-length string is different from NULL, they both are blank values;
Microsoft Access allows you to distinguish between two kinds of blank
values: Null values and zero-length strings. In some situations, a blank
value indicates that the information might exist but is not currently
known. In other situations, a blank value indicates that the field does not
apply to a particular record.

For example, if a Customers table contains a Fax Number field, you can
leave the field blank if you're unsure of the customer's fax number, or
whether the customer even has a fax. In this case, leaving the field blank
enters a Null value, which means you don't know what the value is. If you
later determine that the customer doesn't have a fax, you can enter a
zero-length string in the field to indicate that you know there is no value.

We can assign a zero-length string to a String type variable, but you
cannot assign a Null value to that same variable - an attempt to do so
would generate a run-time error. You can assign Null values only to
"Variant" type variables; we can also use IsNUll function to determine
whether an expression or control contains a NULL value. Could you use Len
function to determine if the field is zero length string?

If IsNUll(var) then

'code

Elseif 0= Len(var) then

'code

End if

Please feel free to reply to the threads if you have any questions or
concerns.


Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.



--------------------
| From: "Peter Carlson" <[email protected]>
| References: <e#JHzn#[email protected]> <1MCGK9GbDHA.2140
| X-Tomcat-NG: microsoft.public.access.queries
|
| No, the issue stems from the fact that Access doesn't treat a 0 length
| string as null - that's prettry weird. So I can fix the "br" problem with
| an IIF statement. - That's the first problem. I dont mind using an IIF
| statement, except that it's not ANSI SQL.
|
| The second problem is much more troublesome (as shown below). If the
field
| is empty (0 length) then the where clause causes the select to retrieve 0
| rows since there is no record where actions2=tIncidents_Actions.Code
|
| Peter
|
|
| | > Hi Peter,
| >
| > If I understand the issue correctly, it seems you query has hard coded
| > <br>, so <br> will be added even if the fields (actions1 to actions4)
are
| > empty/blank;
| >
| > (Actions1) & ('<br>'+Actions2) & ('<br>'+Actions3) & ('<br>'+Actions4)
as
| > Actions
| >
| > Please correct me if I misunderstood it.
| >
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| >
| >
| > --------------------
| > | From: "Peter Carlson" <[email protected]>
| > | Subject: MultiTable Query
| > |
| > | I've been baning my head with this query.
| > |
| > | We have a table with the following fields:
| > | Table:Incident
| > | id: autonumber id
| > | person: reference to Person.ID
| > | role: reference to Roles.ID
| > | action1: reference to Actions.Code
| > | action2: reference to Actions.Code
| > | action3: reference to Actions.Code
| > | action4: reference to Actions.Code
| > | ... many more fields not pertinent to the question
| > |
| > | Table: Person
| > | id, last, first, ...
| > | 3, Carlson, Peter
| > |
| > | Table: Roles
| > | code, descr
| > | 1, Major Role
| > | 2, Minor Role
| > | ...
| > |
| > | Table: Actions
| > | code, descr (code is 2 char)
| > | 1, Did something
| > | 2, Did something else
| > | NN, Did nothing
| > |
| > | So assuming the following entry in the Incident table:
| > | 1, 3, 2, 1, NN, , ,
| > | when I retrieve the sql (for html report) I want the following
| > | id, fullname (Last + First), Descriptive Role, Desriptive Action1-4
| > | separated with <br> if they exist
| > | 1, "Carlson, Peter", Minor Role, Did Something<br>Did nothing
| > |
| > | So far we are using the following SQL which pulls most everything
| > together:
| > | SELECT IncidentNumber, FORMAT(I.Arrived, 'hh:nn') as Arrived,
| > | FORMAT(I.Cleared, 'hh:nn') as Cleared, A.name AS apparatus, r.descr AS
| > role,
| > | B.descr AS Actions1, I.Actions2, I.Actions3, I.Actions4, (Actions1) &
| > | ('<br>'+Actions2) & ('<br>'+Actions3) & ('<br>'+Actions4) as Actions
| FROM
| > | Incidents_Apparatus AS I, Apparatus AS A, tApparatus_Use AS R,
| > | tIncident_Actions AS B WHERE I.IncidentNumber='%id%' and
I.Vehicle=A.id
| > and
| > | I.Role=R.code and I.Actions1=B.code and I.Actions2=B.code and
| > | I.Actions3=B.code and I.Actions4=B.code;
| > |
| > | However we have 2 problems:
| > | 1. the concat of Actions 1-4 always adds the <br> even if the field
is
| > | empty (doesn't appear to be null, but appears as LEN(0) ).
| > | 2. if Actions1-4 is empty, then it is not able to find an appropriate
| > entry
| > | in the Actions Table (because there is no empty action)
| > |
| > | I could add an "empty" row to the Actions table because the code
column
| > is a
| > | 2 char field, however if the code column was an autonumber ID like it
is
| > in
| > | most of our other tables then I couldn't add an empty field. I could
| also
| > | spatter my SQL with IIF to test the LEN of Actions1-4 and then concat
| > | together, but that's not ANSI sql and wont work outside of MS.
Although
| I
| > | already need to maintain 2 tables of SQL, our product on the "heavy"
end
| > | supports oracle and mySql. The lite version uses Jet to an access
| > database.
| > | access doesn't use DATE_FORMAT and mySQL doesn't use FORMAT.
| > |
| > | Is there any way to accomplish this?
| > |
| > | Thanks, and sorry for the lengthy post.
| > | Peter
| > |
| > |
| > |
| >
|
|
|