Unmatched Querry Wizard (I promise I googled)

[hrenee]

I'm not actually sure I'm using the right querry wizard and as I dig deeper into Google, I'm even more confused (and quickly realizing how much I don't know!). Here's the scenario (and yes I've googled repeatedly, read a ton and just don't understand what I'm doing wrong):



I was given 2 tables: 1) is a list of fixed asset from corporate. Basically it has a unique identifier PK of AssetID, with a bunch of info about that asset. 2)Is a list from the Auditor about the fixed assets, also each with a primary key.

My job (I'm an accountant) is to figure out, which of the auditor list is on the corporate list. So I've added (and made a relationship) AssetID to the Auditor table and as I figure the AssetID out I write it in.

It's now become apparent that the auditor missed a few pieces of equipment himself. When I'm done assigning all the auditor's list an AssetID I'm certain there will be assetID's left in Table1 (above) that haven't been used and I want to run a querry to find them.

I *thought* it was as easy as running the unmatched querry wizard with the AssetID in Table 1 compared to AssetID in Table2 but it just returned every single line, and not the ones that haven't been used left. I've read and I've read and I just can't grasp what I'm doing wrong. . .

I know I'm probably thinking about this all sorts of wrongly so please help a girl out.

Thanks in advance.

[June7]

First, the AssetID fields in each table are NOT both autonumber? You are entering AssetID as FK into auditor table?

Show the query statement for analysis. It should join on the AssetID fields, join type "Show all records from Assets ...", and have criteria under the Auditor AssetID field of Is Null.

[hrenee]

First, the AssetID fields in each table are NOT both autonumber? You are entering AssetID as FK into auditor table?

Show the query statement for analysis. It should join on the AssetID fields, join type "Show all records from Assets ...", and have criteria under the Auditor AssetID field of Is Null.

Here is the SQL:

SELECT tblFixedAssetListing.[Asset Num], tblFixedAssetListing.[Asset Description]
FROM tblFixedAssetListing LEFT JOIN tblAppraiseAssetDetail ON tblFixedAssetListing.[Asset Num] = tblAppraiseAssetDetail.[AssetID]
WHERE (((tblAppraiseAssetDetail.AssetID) Is Null));


No the numbers are not autonumber. It's an imported # from corporate assigned to each asset.

[June7]

The SQL looks good. Guess need to see data. If you want to provide, follow instructions at bottom of my post.

[hrenee]

The SQL looks good. Guess need to see data. If you want to provide, follow instructions at bottom of my post.

Ok I'll clean the data and get it in here. It's a HUGE file. So I may just put like 10 of each in if that's ok.

[June7]

Fine, as long as it reproduces the issue.

[hrenee]

I feel really stupid but I can't get the zip file to upload. If I upload the whole zipped folder it's too big and the individual file inside, keeps saying it isn't found. It's possible I'm just an idiot (highly probably even) and I don't in fact know how to zip a file. . . help (she says meekishly)

[June7]

Make sure you run Compact & Repair on the db first.

Then from Windows Explorer, right click in a folder > New > Compressed (zipped) Folder. Then click on the db and drag the file into the new zip folder.

[hrenee]

I just pressed the compress and repair, and it didn't actually seem to do anything. Was it supposed to take a while? Anyway, the way you said to create the zip file is what I did, and it if I open the zip folder and chose the DB it says file not found. . . I'm really feeling stupid at this point.

[June7]

I have your db. Compact & Repair usually very fast but can take a few seconds for larger db. Files must first be EXTRACTED from zip folder then can open.


Why does AppraiseAsset table have a bunch of blank rows?

Problem appears to be the AssetNum field in tblFixedAssestListing is retaining what appears to be two spaces at the end of each value. I did a Find/Replace to remove the spaces. Be sure to select 'Any Part of Field'. A space was removed but there is still something there and Access is not seeing it as a space. So it is some other character. Do this fix.

1. Create a new field in the table.

2. Run an UPDATE query to populate the new field with this expression: Left([Asset Num],5)

3. Delete the old field

4. Make new field the primary key

5. Build Find Unmatched with the new PK field.

[hrenee]

thank you, I'll try it now. I need to run a querry to find all the blank fields. I inherited this DB from my boss, and I don't know why it has them. when I inherrited it, there were no relationships or anything so I've tried to piece it together best I can with what I've been given.

I was wondering how you found that issue? The tbl is from an excell spreadsheet, which comes as far as I know from an Oracle database at headquarters. Is it possible it's been inserted there or is it something I managed to do to the DB?

Oh and it can be marked as solved. Thank you.


Oh and can you please explain the Left([Asset Num],5)

While I'm sure it works, I like to understand things and I don't understand what/how this fixes the problem (particularly the 5)

[June7]

After viewing the tables and query, I simply developed a suspicion, so I put cursor in the field and behold - extra spaces!

Left() is one of a number of string manipulation functions that Access recognizes. Access Help has more guidelines or search the web.

The expression shown simply extracts the first 5 characters of the value and discards the remainder.

I doubt this resulted from anything you did in the import. I suspect something about the Oracle data is cause, Excel dealt with as best it could.

[hrenee]

Oh ok I get it now, it wasn't the Left that had me confused it was the 5, but I got it. Thank you! It's becoming apparent that my 2 DB classes in college left me wofully unprepared.