join properties cause type mismatch

[kkjewell]

Okay, I apologize, I'm not extremely Access knowledgeable so bear with me.

I have an Access database I created which I thought was working okay, then I realized it was missing one vendor because we haven't started writing checks to that vendor yet and probably won't for a while.

I am using a make-table-query to create a table that combines data from two linked tables. The two linked tables have a matching field (VendorID) one linked table is "History" and the other table is "Master" (these tables are SQL data from MS Great Plains if you need to know that).

Now my problem ... not every VendorID in Master table is present in History table.

I first created the join statement to say use all records in History and only those records in Master that match. This works without any error statements, however, I discovered there is a significant field of detail that is being omitted because a VendorID is in Master, but since no checks or invoices have been created for that VendorID it does not exist in the History table yet.

I decided to change the join properties to say "include all records in Master and only those records in History that agree". When I try to run the query I receive the error message "data type mismatch". I feel certain it is because several of the fields have no information to draw from on the vendors that don't exist in the History table.

What I need the query to do is go ahead and use the information from the Master table and when there is no matching VendorID in the History table use a zero for those fields.

I used to program and know a little C+. I understand "if" statements and feel like a sql if statement could work around this (for example, if History.VendorID does not exist zero, else History.[fieldname], but I don't know how to go about doing it. Any help for a novice would be much appreciated. Thanks in advance

 

[boblarson]

The data mismatch is telling you that you've tried to link two fields together that don't have exactly the same datatype.

 

[kkjewell]

I understand, but the only change I have made at all is the change in the join statment between the two tables, so the mismatch has to be that there is no data in the second (History) table. Absolutely nothing else changed.

 

[boblarson]

Still, double check, IN THE TABLE, the fields you are linking to see if they are exactly the same -

For example -
text with a length of 5 will not match with a text with a length of 10
numbers - Long Integer will no join with a Double

And you, yourself, just stated that there was no change EXCEPT in the join statement - were the two joined before?

 

[kkjewell]

sorry I had to go to a meeting so am just getting back to this

Yes they were joined before and worked fine. At that time the History table what the "first" table.

Now literally the only change was to make Master the first table. By "first" I mean the one to which the query looks to pull all the vendorID's

 

[boblarson]

Can you post your db to look and see. I think we're probably just not communicating something.

 

[kkjewell]

Sure, however I'm sure the linked tables won't work once I send it somewhere. Also I'm not sure how to attach it. The attachment button below says it supports several file types, but .mdb is not one of the options. Should I just attach it anyway or is there another way to send it to you.

 

[KeithG]

What you have done is change the join from an INNER JOIN to a LEFT OUTER JOIN but this would not change the data type of your data. Either the VendorID in the two different tables are of different data types or you are supplying the wrong data type in the criteria expression.

 

[KeithG]

run the file through WinZip.

 

[boblarson]

If this is a split database, you'd need to include the applicable backend tables as well and you can do that by making a copy of your database and then going in and deleting the links and use the import Wizard to import them.

Then, you want to use Tools > Database Tools > Compact and Repair and that will compact the database. Then, you have to zip it with WinZip (or something like it - WinXP has a built-in zip utility) and THEN you can upload it here. Make sure you're on the advanced editing mode for your message (you can go to the quick post and click Go Advanced) and then down under the message there is a button to Manage Attachments.

The final compacted and zipped file has to be less than 394Kb

 

[boblarson]

What you have done is change the join from an INNER JOIN to a LEFT OUTER JOIN but this would not change the data type of your data. Either the VendorID in the two different tables are of different data types or you are supplying the wrong data type in the criteria expression.

I forgot to mention the criteria, if there is any.

 

[kkjewell]

Okay the first attempt at getting it to you created 2.2M file after zipping it. I had to laugh. Anyway, now I've taken out everything except the tables and queries you need and it's still 2,223K. Any suggestions?

 

[boblarson]

I'll PM you my email address and you can send it to me and I'll take a look.

 

[boblarson]

 

[kkjewell]

Bob, thank you so much. I can't believe it was something that small. I was so sure it was a really advanced problem way above my head.

Why would that only show up when I changed the join statement. Seems like it should never have run in the first place.

Anyway, thanks again, Kathy

 

[KeithG]

I though it had something to do with the criteria!