Query is not responding (Updating)

[Sniper-BoOyA-]

Good Morning / Afternoon,

Ive got the following query:

SELECT labgegevens.monsternr, tblstabilisatiecilinders.*, (([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100 AS watergeh, (([massa]/(100+[watergeh]))*100000)/[opvinh] AS drgdchthd, tblstabilisatiecilinders.opvnr, tblopv.opvgew, tblopv.opvinh, ([totaaldruk]/[opvgew])*1000 AS d7, ([totaaldruk]/[opvgew])*1000 AS d28, Round([d7],1) AS rondd7, Round([d28],1) AS rondd28, labgegevens.proefnummerstab, labgegevens.proefnummerstab
FROM tblopv RIGHT JOIN (tblstabilisatiecilinders RIGHT JOIN (labgegevens LEFT JOIN tblwatergehalte ON labgegevens.monsternr = tblwatergehalte.monsternr) ON tblstabilisatiecilinders.monsternr = tblwatergehalte.monsternr) ON tblopv.opvnr = tblstabilisatiecilinders.opvnr;

But its not responding to anything i do. And when i remove table tblopv (in design view) it works great.

Any ideas?

 

[JamesMcS]

So it just hangs? Can you post the DB, stripped out with a bit of sample data in it?

 

[Sniper-BoOyA-]

Here it is..

Ive stripped it down to just tables and 2 queries.

Querystabilisatieinvoer is the one that is not working correctly.

What i wanted to achieve is that all the monsternr's made in table labgegevens will be displayed in the table stabilisatiecilinders.

But if i change the join properties to

labgegevens ..................stabilisatiecilinders
monsternr------------------> monsternr

Then the query just locks up.

If i change it back to

labgegevens ....................stabilisatiecilinders
monsternr------------------monsternr

It works great.

 

[Sniper-BoOyA-]

To clear things up a bit i'd like to attach two image files.

Works is an image of the designview of the query, where the query works, but doesnt have the end result that i would like.

Worksnot is an image of the designview of the query, where the query just locks up.

 

[vbaInet]

It works ok on my computer.

You need to go back to your fields and define their data types properly and set up relationships between the tables. For example, you've made Massa in tblstabilisatiecilinders a Text data type when it actually should be a Number (Double) data type plus you're performing mathematical calculations on that Text field. I've not looked at the rest of the fields but get the above (and others) sorted first.

 

[Sniper-BoOyA-]

Thanks, ill have look.

 

[Sniper-BoOyA-]

Ok,

Ive changed the Massa field to a Number data type. I checked the relations between the tables (as in the screenshot attached in the previous post)

I also ran a quick check through all the other tables to see if there were any data types that needed to be changed.

But when i fire up the query, it still doesnt work. It just doesnt want to update the tblstabilisatie table, thats what it seems like anyway. I can do it manually (Monsternr's from querywatergehalte), but thats not an ideal solution.

The thing i still do not understand is the fact that it does work, when i remove tblopv out of the query.

Back to the drawing board i go..

 

[vbaInet]

I checked the relations between the tables (as in the screenshot attached in the previous post)

By checking it I meant create relationships in the Relationships window and set referential integrity where necessary.

But when i fire up the query, it still doesnt work. It just doesnt want to update the tblstabilisatie table, thats what it seems like anyway. I can do it manually (Monsternr's from querywatergehalte), but thats not an ideal solution.

You need to clarify what you're trying to do. Your initial post indicated that the problem you had was to do with not being able to view the query but now you mention not being able to Update it. Please clarify.

 

[Sniper-BoOyA-]

Allright:

Ill try to clarify it for you.

The lab gets 9 samples. They will number each sample by using a form which is based on the table Labgegevens. Each sample number will also get a monsternr which is a autonumbering field.

The next step is to determine the watercontent of each sample. Thats where the tblwatergehalte and querywatergehalte come in play.

What Querywatergehalte basically does is, it gets all the monsternr's from labgegevens. And then the person who is entering the data can calculate the watercontent. (see querywatergehalte)

The 2 methods explained above are pre-tests. These tests will be necesarry to complete the following test.

Now, this is where the problem is located. Or at least, its not functioning. Ive got a table tblstabilisatiecilinders, with a few fields. One of them is Monsternr.

I would like to use the same monsternr's as in querywatergehalte.

There is also a field that uses a lookup function to look in tblopv. Tblopv is a table where all the cilinders are stored. In that table you can see the capacity and the surface area of the cilinders in question.

Anyways, the idea is, to let all these tables come together. (see querystabilisatieinvoer)

- I want a list of all the monsternr's from querywatergehalte.
- The watergehalte of each monsternr will be shown
- According to the monsternr's the sample numbers given by the person
who entered the data will be shown.
- I want to choose the Opvnr (lookup function), if i selected one, the information from tbl opvnr will be shown. , for each monsternr.
- I want to choose the mengselverh , for each monsternr. (mixture proportion)
- I want to enter the total pressure , for each monsternr. (totaaldruk)

And last bot not least, there are also formulas to calculated the maximum pressure of the sample. That formula is based on most of the information above.

Now;

If i setup the query as follows:

Querywatergehalte__________tblstabilisatiecilinders______________opvnr
_____________________________________________________________
monsternr ---------------------> Monsternr________________________
_____________________________opvnr-------------------------> opvnr

I get a list of all the monsternr's made in labgegevens/querywatergehalte. But i cannot enter any data in the query or the form. It doesnt update the query anymore. But, if i take the table opvnr out of the query, it works fine.

But if i setup the query as follows:

Querywatergehalte__________tblstabilisatiecilinders______________opvnr
_____________________________________________________________
monsternr --------------------- Monsternr________________________
_____________________________opvnr------------------------- opvnr

Obviously, i wont get a list of all the monsternr's from labgegevens/querywatergehalte. But i can manually enter all the monsternr's just fine. Plus enter the data needed without a problem.

I hope i clarified it a bit more.

And thanks for all the time and effort, and most of all patience.

 

[vbaInet]

I'm still having difficulty understanding what your problem is. Bear in mind that most of us don't have time to read long messages so short and concise messages always help

Have a look at this:

http://allenbrowne.com/ser-61.html

 

[Sniper-BoOyA-]

ok, the problem in one sentence:

I cant enter any data. Unless i either take tblopv out of the query, or change the relation between the tables.

Wups thats 2 sentences

 

[vbaInet]

Much better

Did you read the link? It tells you the different scenarios that would make a query read-only.

 

[Sniper-BoOyA-]

Yes, i did actually.. Printed it out, going through them as we speak.

Cheers!