Problem with multitable query (1 Viewer)

Hi there I'm hoping that someone can help me with this one. It's the first time I've had to create a database and I know zilch about coding so tried to do everything using the wizards within Access 2007 and a book from the library (some of the code from which wouldn't work when I tried to use the particular part of the database so I had to delete it). I had to use lookup fields for some of the tables as I knew that when it came to viewing information in the associated forms my boss would say that loads of numbers rather than words didn't mean anything to him.

The forms are feeding the info into the tables fine but when it comes to the multi-table queries, although they display plenty of information when they are first opened, if the design view is entered and you ask it to just throw up the results for say all those on the books in a certain area who are plumbers, and click the run button, the results screen comes up blank even though on being originally opened several of that trade are clearly showing. I've got no idea what is going on and I spent a good hour or more trying to figure it out while the boss was out of the room, let alone the time I've spent since arriving back home.:banghead:

I did think about using one of those search by form queries but our sales consultants couldn't come to an agreement on what parameters they wanted to search by but said they would want to see a lot more information about each worker as a result. So muggins here is left to try to sort it all out but needs help.

I have acc2003 so can not open or read an accdb file.
Can you post the sql for your query?

Also you could search on only a few fields - enough to identify the records you need - and then display all the data for those records.

You know - it would be mighty helpful to try to diagnose the problem if you had put some test data into the database. Without any data it isn't going to show anything at all for us so we can't help all that much.

But the first thing I do notice is that you have used LOOKUPS directly in some fields at TABLE level. You should NOT be doing that. You should remove them. See here for why:
http://www.mvps.org/access/lookupfields.htm
And here for how to remove them:
http://www.btabdevelopment.com/ts/removelookups
You should use lookups ON FORMS but not in tables directly.

Hi, I didn't realise that I could get up more fields than the number of parameters passed from a form.

The SQL for the current search query is:

SELECT tblWorker.[Worker Name], tblWorker.[Town/City], tblWorker.County, tblWorker.Landline, tblWorker.[Mobile(s)], tblWorker.CSCS, tblWorker.CV, tblWorkerTrade.[Trade Name], tblWorkerTrade.[Trade Notes], tblWorker.Available, tblWorker.[Worker Notes]
FROM tblWorker INNER JOIN tblWorkerTrade ON tblWorker.[Worker ID] = tblWorkerTrade.[Worker ID];

if that means anything!

Thanks for those links. I will go and remove the lookups from the table now and feed some dummy data in. I had copied the one that I was using at work today with some data in to my memory stick and brought it home but for some reason it won't open on my home pc. Possibly because my machine at work has got 2010 on it whereas my colleagues machines and my home one all have access 2007! I will make the alterations you mentioned and feed some dummy data into it before doing a rezip and repost if that's ok?

Ok, I've gone back into the tables and removed all the look up fields and tried to get them into the forms as combos instead. I've put in some dummy data and have also added an extra table into the query which now doesn't show any data at all! Even if I put the name of a county which I know is in the tables in and tell it to run the results are coming up empty! The new zipped database is attached

Okay, here's the scoop.

1. Your relationships needed a little work. (see below)
2. TradeName needed to come out of tblWorkerTrade as well.
3. You didn't have any TradeID in the tblWorkerTrade for any of the workers in there, and especially not the ones from Devon.
4. In the Worker Trade Form - your combo was not bound to TradeID which it should be.
5. The same goes for your controls in Placement History. They need to be bound to the fields they are displaying. But you should lock those controls since that info is set elsewhere.
6. In the combo named TRADE NAME on the Placement History subform, revised the combo's ROW SOURCE to use the TWO tables so the name can be displayed.
7. Changed the Row Source for the combo named CLIENT NAME on the placement history subform. You don't use the table where the data is being stored, you use the table in which are stored the lookup values.

The Pic for #1 of this list:

Thank you very much for taking the time to look over this for me! I think I understand most of what you are saying, the only bit I'm a bit stumped on is the using two tables as a row source for trade name in the Placement History subform as the wizard didn't give me chance to select a second table for row source. Do I take it that I would need to go for the two identifiers in the trade table to get the trade name along with the worker ID from the workertrade table with the way its set out now? Also I don't really understand why the workertrade table is no longer linked to the workerclient table.

Sorry if I'm coming across as a bit dense here!

kimberlin said:

Thank you very much for taking the time to look over this for me! I think I understand most of what you are saying, the only bit I'm a bit stumped on is the using two tables as a row source for trade name in the Placement History subform as the wizard didn't give me chance to select a second table for row source. Do I take it that I would need to go for the two identifiers in the trade table to get the trade name along with the worker ID from the workertrade table with the way its set out now? Also I don't really understand why the workertrade table is no longer linked to the workerclient table.

Sorry if I'm coming across as a bit dense here!

Click to expand...

No, you're not dense. It was me being dense. You should actually only have ONE table there and it should be the tblTrades table. My bad.

ok now I'm even more confused as some of our workers have several trades that they are registered as doing (maybe I should have put some dummy data in to show that), so I thought that would need a many to many relationship between worker and trade?

kimberlin said:

ok now I'm even more confused as some of our workers have several trades that they are registered as doing (maybe I should have put some dummy data in to show that), so I thought that would need a many to many relationship between worker and trade?

Click to expand...

You already do have a many-to-many relationship. Take a look at the relationships I put up there. With a many-to-many, you actually have a one to many on one side and a one to many on the other side. So the tblWorkerTrade is the junction table where that is stored. The trades combo ROW SOURCE though brings the VALUES you can select for that tblWorkerTrade table which is bound to the CONTROL SOURCE of the combo. Remember Row Source and Control Source are different items. The Control Source is what the control is bound to. The Row Source is where the values are coming which you can select.

So the worker ID from the workertrade table and the two fields in the trade table are the ones I need to get for the row source trade name in the placement history with the control source as workertrade ID? That's the one bit I have got left to sort out now (hopefully!)

kimberlin said:

So the worker ID from the workertrade table and the two fields in the trade table are the ones I need to get for the row source trade name in the placement history with the control source as workertrade ID? That's the one bit I have got left to sort out now (hopefully!)

Click to expand...

I think you have it. The combo box has the row source of the two fields from the trades table: TradeID and Trade Name. The combo box is bound to the TradeID field in the tblWorkerTrade which is the form's record source.

According to the properties tab for the placement history subform, its record source is tblWorkerClient and its the combo box for trade within that which is the one I'm trying to do as in your point 6 of post 7 in this thread!