OK, here's a weird one... I'm building an application for creating estimates and work orders. I have a jobs table which has an autonumber for a primary ID. I've tried building a query that pulls all the job data from the main jobs table (tbl_film_jobs) and 4 tables which describe the separate scopes of the job (tbl_film_si, _s2, etc) which pull the primary key from the main table when entered in the form (using subforms for each scope table).

I got the Type Mismatch error the first time I tried running the query, so I decided to try adding each field to the query one at a time and running it to see where the problem was. Now here's where it gets perplexing...

The first column in my query is just film_job_ID from the tbl_film_jobs table and that is giving me the "Type Mismatch in Expression" error. There is no expression yet. Any idea on how to troubleshoot this?

Thanks

What is the data type for the film_job_ID column?

If it's numeric, having any non-numeric character in the data being entered will cause an error. For example 122-344 would error, because of the hyphen.

Try typing the value directly into the table and see if you get a similar error.

Thanks, Alc

Data type is Auto Number, field size is Long Integer, New Values is Increment and format is General Number. It will not let me enter a number into the field.

1. Do a compact and repair on the database.

2. Reboot your computer.

3. If that fails, try importing everything, except your current query, into a blank new database file and then recreate the query.

4. And if that fails, try posting the database here so we can play with it to see what else might be up.

I think that's your problem.

Auto Number is just that, an automatic number that gets assigned by Access. From Access Help:

A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated.


Is there any data in this table yet? If not, I'd change this field to be another number type.

I think that's your problem.

Auto Number is just that, an automatic number that gets assigned by Access. From Access Help:


Is there any data in this table yet? If not, I'd change this field to be another number type.
That doesn't make sense to me. He's just trying to get a query to work not trying to insert anything (at least from the posts I read I didn't get that). An autonumber should not be returning a type mismatch for a query you are trying to pull data from. So, something is not right and it could be corruption.

That doesn't make sense to me. He's just trying to get a query to work not trying to insert anything (at least from the posts I read I didn't get that). An autonumber should not be returning a type mismatch for a query you are trying to pull data from. So, something is not right and it could be corruption.
You're quite right. I read the original question too quickly, saw the word 'adding' and went off at a tangent. :o

1. Do a compact and repair on the database.

2. Reboot your computer.

3. If that fails, try importing everything, except your current query, into a blank new database file and then recreate the query.

4. And if that fails, try posting the database here so we can play with it to see what else might be up.

OK, how do I post that database? None of the above seemed to work.

How to upload a database to the forum (http://www.access-programmers.co.uk/forums/showthread.php?t=140587)

Thanks! Here it is. There's only one query in there which is the one I'm having trouble with. I'm still a bit of a noob, so any other critique would be greatly appreciated.

Okay, just so you know - the mismatch error does NOT have to do with your autonumber field. It has to do with one or more links that are between tables. This may take a little bit to get into as on first look it looks like you might have some normalization problems with your database.

Okay, first thing up is to get this database normalized. You should NOT have repeating fields or tables. Read this first (http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf) to get a grasp on normalization and then we can work at trying to redesign the tables so they are correct. This is important because you cannot easily get data out (especially in a meaningful way) if you don't have a normalized design.

I really appreciate the help. That doesn't surprise me. I was using the sidemark field as the primary key but learned that it was better to use an autonumber, and I had to change a bunch of columns and relationships to make that change.

OK, based on that document, here's what I think I need to do...

1) Change the names of the foreign keys to be the same as the Primary keys
2) Set the default values of the foreign keys as null
3) Create autonumber primary keys for the small lookup tables that aren't using them
4) Rename the tables and fields more effectively
5) rebuild my forms

Am I missing anything important here?

Not just rename tables and fields but completely redesign some and eliminate others.

So, instead of tbl_film_s1, tbl_film_s2, tbl_film_s3 you would have ONE tbl_film table and if a type is needed it would be as a field inside that table.

Same with tbl_shade_s1, tbl_shade_s2, etc. and then clients and contacts really probably should be in one table and you will end up in one of the other tables (not sure which right now) being able to add as a client.

Also, you would have tblJobs but you would also have a separate Junction table for assigning Employees to the jobs so you have a many-to-many capability.

We'll probably need to go through a few iterations of this to get it narrowed down to the right tables and fields and I would work on getting the entities and attributes set first so we can do the design on paper (so-to-speak) which then the rest just follows off of. Your first thing should be table design. You identify ENTITIES which end up making up tables and then the ATTRIBUTES of those entities which are then the columns for the tables.

I'll do the best I can to help and hopefully more will show up and assist too because it is important to get the design straight BEFORE going to the next step.

OK, I think I'm getting it. Keeping the scopes within the job table makes sense.

As far as contacts and clients, we work with multiple contacts within each client. Would I still want to keep those in a single table?

With the employees, there are really only 3 people who are going to be estimating jobs, so I'm not sure about this junction table (I don't really know what that is!). I'll do some tinkering and upload the new version in a bit. I also wonder if I need to start a thread for this outside of the queries forum.

So I've been spending the last couple of hours trying to streamline and normalize the tables, and I've posted it all to a new thread in the tables forum here: http://www.access-programmers.co.uk/forums/showthread.php?p=857650#post857650

Thanks again for all the help!