Query Problem (1 Viewer)

[sparklegrrl]

I have a query from two tables.

Table A has

WorkOrderID
CustomerID
CarInitialID
CarNumber
TrailerNumber
etc...

Table B has
CarInitialID
CarInitial

Since I the WORKORDERS table saves the CarInitialID as a number, I have created a query to use to display the data on top of forms.

I have a query that has the WorkOrder ID, CarInitialID, CarNumber and Trailer number from the WorkOrders Table. It also has the CarInitial from the Car Initials Table so that it will display correctly.

I have the CarInitialID from WorkOrders linked to CarINitialID from the Car Initial ID table.

When I click datasheet view it only displays those jobs who have a car initial id & a car number but no trailers.

When I delete the link, it displays all the jobs but it just throws any old car initial in that field.

I need it to list all the jobs.

What am I doing wrong?

 

[jadair]

It sounds like there is something wrong with the Query. Is there any way you can post the DB or something similar?

 

[jadair]

Did you check the original query that has "Trailers". look at it in design view and make sure the SHOW box is checked otherwise you cannot see it.

 

[sparklegrrl]

The DB is too big for our slow upload speeds but here are some screenshots.

Here is the query:

Here is the datasheet view that matches the query, it is okay except that it doesn't show the jobs that are trailers.

And here is the datasheet view w/the relationship deleted - WAY WRONG. car ID's everywhere!

 

[jadair]

IS the Car ID more than one number?

 

[sparklegrrl]

I suppose it could be. That is the foreign key in the WORKORDERS TABLE and the PRIMARY KEY in the CarInitials table.

 

[jadair]

I cant see why it isn't working from here. If you can post the tables I can try and create new queries.

 

[sparklegrrl]

Actually...here's a copy from last week I think...I dunno if that query was in there yet or not.

Http://users2.ev1.net/~dls4967/newchandler.zip

 

[jadair]

Where are these tables in the DB and What are they called?

 

[sparklegrrl]

WORKORDERS
and
CARINITIALS

 

[jadair]

Here is your problem. Car Initial ID cannot be NULL. i.e. cannot contain 0 or be blank, it is a primary key in the other table, you cannot have a null value in a primary key. If you make these changes then the query returns all 9 records. I only see one record with a trailer number in the original table and that is displayed. I tried show all comands and they don't work unless you make these changes. Hope this helps

 

[sparklegrrl]

I'm not quite understanding how to fix it...please excuse the ignorance.

 

[jadair]

Table A has

WorkOrderID
CustomerID
CarInitialID
CarNumber
TrailerNumber
etc...

Table B has
CarInitialID
CarInitial


In Table A change all CarInitialID fields so that they are greater than 0 and not blank. when you link the two tables together you lose all the info where Car Initial is 0 or blank because there is nothing in the other table for it to relate to. This the result I ended up with Look at the Car Inital Column(See attachment)
Let me know how you make out