It's okay if it's blank . . .

[hrenee]

Ok here's the SQL code:

SELECT tblFixedAssetFMV.DEPT, tblFixedAssetFMV.MainID, tblFixedAssetFMV.AssetID, tblFixedAssetFMV.DESCRIPTION, tblFixedAssetFMV.Field1, tblFixedAssetListing.[Asset Description]
FROM tblFixedAssetListing INNER JOIN tblFixedAssetFMV ON tblFixedAssetListing.AssetID = tblFixedAssetFMV.AssetID
WHERE (((tblFixedAssetFMV.AREA)="CAN" Or (tblFixedAssetFMV.AREA)="CAWH")) OR (((tblFixedAssetFMV.AREA)="PEA") AND ((tblFixedAssetFMV.DEPT)="NCK"))
ORDER BY tblFixedAssetFMV.DEPT;




Ok what I'm asking is for a list of equipment in certain departments, and if the AssetID in the FMV TBL matches the AssetID in the fixedAsset table, give me the description (there is a one to many relationship between the AssetID in fixed asset listing and the FMV table).

But I'm also okay with having the description blank if the AssetID's don't match. . . And right now it's only returning ones that do have a match. How do I tell it if blank, return a blank in description?

I'm thinking I need something like IF null then something but I don't know how/where to put it.

[Dal Jeanis]

SELECT 
   tFMV.DEPT, 
   tFMV.MainID, 
   tFMV.AssetID, 
   tFMV.DESCRIPTION, 
   tFMV.Field1, 
   NZ(tL.[Asset Description],"") AS [Asset Description]
FROM 
   tblFixedAssetFMV AS tFMV
   LEFT JOIN
   tblFixedAssetListing AS tL
   ON tL.AssetID = tFMV.AssetID
WHERE (  (tFMV.AREA="CAN") 
      OR (tFMV.AREA="CAWH") 
      OR ((tFMV.AREA="PEA") AND (tFMV.DEPT="NCK"))
      )
ORDER BY tFMV.DEPT;

[hrenee]

It didn't give me any errors but it didn't return anything either.

It should return 144 lines and it is only returning the 87 that have matching AssetID.


could I possibly have done something wrong in the relationship? Could I "solve" this by creating a blank AssetID field in tL?

**ETA** I know I can't put a blank AssetID because it is the PK in that table and therefore cannot be blank. Could that be an issue?

[Dal Jeanis]

Let's check assumptions. Run this and see what you get

SELECT 
   tFMV.DEPT, 
   tFMV.MainID, 
   tFMV.AssetID, 
   tFMV.DESCRIPTION, 
   tFMV.Field1
FROM 
   tblFixedAssetFMV AS tFMV
WHERE (  (tFMV.AREA="CAN") 
      OR (tFMV.AREA="CAWH") 
      OR ((tFMV.AREA="PEA") AND (tFMV.DEPT="NCK"))
      )
ORDER BY tFMV.DEPT;

[hrenee]

SELECT tblFixedAssetFMV.DEPT, tblFixedAssetFMV.MainID, tblFixedAssetFMV.AssetID, tblFixedAssetFMV.DESCRIPTION, tblFixedAssetFMV.Field1
FROM tblFixedAssetFMV AS tblFixedAssetFMV
WHERE (((tblFixedAssetFMV.AREA)="can" Or (tblFixedAssetFMV.AREA)="cawh")) OR (((tblFixedAssetFMV.AREA)="pea") AND ((tblFixedAssetFMV.DEPT)="nck"))
ORDER BY tblFixedAssetFMV.DEPT;


That's what I wrote and it returned the proper 144 lines.

But this:
SELECT tblFixedAssetFMV.DEPT, tblFixedAssetFMV.MainID, tblFixedAssetFMV.AssetID, tblFixedAssetFMV.DESCRIPTION, tblFixedAssetFMV.Field1
FROM tblFixedAssetListing INNER JOIN tblFixedAssetFMV ON tblFixedAssetListing.AssetID = tblFixedAssetFMV.AssetID
WHERE (((tblFixedAssetFMV.AREA)="CAN" Or (tblFixedAssetFMV.AREA)="CAWH")) OR (((tblFixedAssetFMV.AREA)="PEA") AND ((tblFixedAssetFMV.DEPT)="NCK"))
ORDER BY tblFixedAssetFMV.DEPT;


only returns 87

[ssanfu]

PMFJI,

But this:
SELECT tblFixedAssetFMV.DEPT, tblFixedAssetFMV.MainID, tblFixedAssetFMV.AssetID, tblFixedAssetFMV.DESCRIPTION, tblFixedAssetFMV.Field1
FROM tblFixedAssetListing INNER JOIN tblFixedAssetFMV ON tblFixedAssetListing.AssetID = tblFixedAssetFMV.AssetID
WHERE (((tblFixedAssetFMV.AREA)="CAN" Or (tblFixedAssetFMV.AREA)="CAWH")) OR (((tblFixedAssetFMV.AREA)="PEA") AND ((tblFixedAssetFMV.DEPT)="NCK"))
ORDER BY tblFixedAssetFMV.DEPT;


only returns 87

You joined another table and there were only 87 records that matched (were equal). If you want the 144 records from table "tblFixedAssetFMV", then you need to change the join type. I always get them mixed up, but it would be the option:

"Include all records from tblFixedAssetFMV and only those records from tblFixedAssetListing where the joined fields are equal"

Just curious: You don't use any fields from the table "tblFixedAssetListing"..... why did you add it to the query??

---------------
BTW, in the first SQL you have "FROM tblFixedAssetFMV AS tblFixedAssetFMV"
This is aliasing the table name with the same table name... its redundant.

Dal used "FROM tblFixedAssetFMV AS tFMV" to reduce the amount of typing (or mistyping in my case).

[hrenee]

I may have forgotten it in something.

Basically I have three lists I'm trying to merge in some way to get an actual list of our fixed assets (I'm an accountant by trade). I have what corporate says we have (that's the asset listing), I have what the auditor says we have, that's the FMV and then I have what Maintenance says we have (that's my MainID).

I've just finished going through our Candy building and trying to match up everything. of the 144 line items that the auditor says we have. I've found 87 of them on our Corporate asset sheet. and I've put that AssetID in a field in that table so I can fill it in as I figure it out. BUT often the description of the corporate asset has no correlation to what the actual equipment is. Like the equipement is a heated canvas table (as per the auditor) but the Corporate list has it as Candy Kitchen Upgrades (please don't ask me the hell I went through to figure out that that is where the canvas tables were purchased under). ANYWAY to make a really long description slightly longer, where I have figured out the AssetID to Auditor's description, I want it to tack the AssetDescription from the corporate table onto the end. BUT that doesn't mean I don't want to see where we don't have a corporate ID which at the moment is what I'm getting.

Ideally it would be

tFMV.MainID, tFMV.AssetID, tFMV.Descripton, tListing.Description(where the tFMV matches an assetID in this table, but blank where it doesn't)

and just for reference Field1 is a pic of the equipment if available.

[hrenee]

OH and thank you, I didn't realize what he was doing. I'm trying very hard to pick this up, but finding my 2 DB management classes in grad school to not be nearly as sufficient as I had hoped.

[hrenee]

OK I think I have something!

This is the problem:
FROM tblFixedAssetListing INNER JOIN tblFixedAssetFMV ON tblFixedAssetListing.AssetID = tblFixedAssetFMV.AssetID

while I need the = there I also need an OR, basically it needs to be the = OR if tListing.assetID /= tFMV.assetID THEN tListing.Description = " "

But I don't know the actual code. . . And will something like that work?

[Dal Jeanis]

Don't worry about it - it takes a while to get comfortable with this stuff.

My left-join in post #2 should not have eliminated any records. Did you paste it in as I coded it, or did you type it in?

If you typed it, did you change the INNER JOIN to a LEFT JOIN and put them in the order that I put them? The table on the left of the word LEFT is the one that keeps all its records.

[Dal Jeanis]

You need SQL that keeps the records from tFMV if there are no matching tL. That's the LEFT JOIN, with tFMV on the left side of the join and tL on the right.

You need SQL that changes a NULL tL.Description from an unmatched record to spaces. That's what the NZ function does in NZ(tL.Description,"").

[hrenee]

I didn't paste it in I typed it, but I will paste it now!

[hrenee]

Apparently, my typing skills leave a lot to be desired. . . That absolutely worked.

you don't have to, but could you possibly walk me through what exactly we were doing in the FROM part of the code? I don't understand the LEFTJOIN in this context.


AND THANK YOU!

This can also be marked as solved.

[Dal Jeanis]

Here's a simple example of how LEFT JOINs work

tblStaffNames
   StaffKey   PK
   StaffName  Text

tblStaffsKids
   KidKey     PK
   KidName    Text
   KidParent  FK to tlbStaffNames

DATA in tblStaffNames
1    George Hamilton
2    Linda Smith
3    Babe Ruth
4    Ernest Borgnine
5    Jane Doe

DATA in tblStaffsKids
100  Jamie   2
101  Kate    2
102  Whoopi  1
104  Ernie   4

This shows the results from a standard INNER JOIN, which only returns records that exist in both tables.

SELECT StaffKey, StaffName, KidKey, Kidname 
FROM tblStaffnames INNER JOIN tblStaffkids ON StaffKey = KidParent

StaffKey StaffName  KidKey KidName
1    George Hamilton  102  Whoopi
2    Linda Smith      100  Jamie
2    Linda Smith      101  Kate 
4    Ernest Borgnine  104  Ernie

This shows the LEFT JOIN keeping all records in the left table, and returning a NULL record if there is no matching record in the right table.

SELECT StaffKey, StaffName, KidKey, Kidname 
FROM tblStaffnames LEFT JOIN tblStaffkids ON StaffKey = KidParent

StaffKey StaffName  KidKey KidName
1    George Hamilton  102  Whoopi
2    Linda Smith      100  Jamie
2    Linda Smith      101  Kate 
3    Babe Ruth        (-)  (-)
4    Ernest Borgnine  104  Ernie
5    Jane Doe         (-)  (-)

This shows the NZ overriding the NULL values.

SELECT StaffKey, StaffName, NZ(KidKey,0) AS KidKey, NZ(Kidname,"(none)") AS KidName 
FROM tblStaffnames LEFT JOIN tblStaffkids ON StaffKey = KidParent

StaffKey StaffName  KidKey KidName 
1    George Hamilton  102  Whoopi
2    Linda Smith      100  Jamie
2    Linda Smith      101  Kate 
3    Babe Ruth          0  (none)
4    Ernest Borgnine  104  Ernie
5    Jane Doe           0  (none)

[Dal Jeanis]

Now, because the field names were all distinct, I didn't have to specify which table each field was in. Fully qualified, the return values would stay the same, and that query would look like this:

SELECT 
   tblStaffnames.StaffKey, 
   tblStaffnames.StaffName, 
   NZ(tblStaffkids.KidKey,0) AS KidKey, 
   NZ(tblStaffkids.Kidname,"(none)") AS KidName 
FROM 
   tblStaffnames 
   LEFT JOIN 
   tblStaffkids 
   ON tblStaffnames.StaffKey = tblStaffkids.KidParent;
StaffKey StaffName  KidKey KidName 
1    George Hamilton  102  Whoopi
2    Linda Smith      100  Jamie
2    Linda Smith      101  Kate 
3    Babe Ruth          0  (none)
4    Ernest Borgnine  104  Ernie
5    Jane Doe           0  (none)

By aliasing the tables, you can make the names much shorter and the query more readable. Again, the results stay the same:

SELECT 
   tS.StaffKey, 
   tS.StaffName, 
   NZ(tK.KidKey,0) AS KidKey, 
   NZ(tK.Kidname,"(none)") AS KidName 
FROM 
   tblStaffnames AS tS
   LEFT JOIN 
   tblStaffKids AS tK
   ON tS.StaffKey = tK.KidParent;
StaffKey StaffName  KidKey KidName 
1    George Hamilton  102  Whoopi
2    Linda Smith      100  Jamie
2    Linda Smith      101  Kate 
3    Babe Ruth          0  (none)
4    Ernest Borgnine  104  Ernie
5    Jane Doe           0  (none)