Need to refer to a column in a query

[tpickles1984]

I'm now writing a query to show all of the values in a table, but only if one of the fields does not match any of the values in another specified query. My coding so far goes like so:

<> ([QryDisplayAllocated]![GI Number])

However, this just throws up a parameter entry box, instead of looking at the column in the query. How can I correct this?

 

[dcx693]

Typically, the notation is: [QryDisplayAllocated].[GI Number]

 

[Pat Hartman]

You can't just reference a field in a query this way, you need to include the query in the query you are writing and specify join criteria.
Open your query in QBE view.
Open the show tables dialog and switch to the queries tab.
Choose QryDisplayAllocated from the list.
Draw a join line from a column in QryDisplayAllocated to the appropriate column in the other table.
That will allow you to reference a field from QryDisplayAllocated in your selection criteria.

 

[tpickles1984]

I've given this a go, but it seems very willing to include the items in the other query, and very unwilling to exclude them. I've attached a cut down sample of my database into this reply, maybe if some context is brought in, it may help in solving the problem.

There are three forms in the database, opening up FrmChooseComponents is your starting point. Here, a main form is shown with a subform (it shows harness models, and the batches of stock used to make them). This is based on three tables; one for the harnesses, one for the components, and one to link them.

Clicking on the "choose batch" button brings up the last table, showing a selection of stock that can be associated with the harness. It is currently based on a simple query, that only shows components that are ticked as "available", from their table.

When the "use" button is clicked, the "GI Number" is transferred into the main subform, and a new association is made, creating a record in the linking table. This is where the problem lies, because if you look at the GI numbers, you'll notice that there are already records containing these in the subform. If you try to select these, you get a nasty error message about duplicate values. I would like to restrict the query so that when the selection form opens up, these records do not appear.

Problem.zip

This link should work, the file is a little to large to upload. I hope the explanation isn't too long winded, any help would be greatly appreciated!

 

[tpickles1984]

If you want to send a database back to me, please use the address:

TP46673@trurocollege.ac.uk

Thanks a lot!

 

[Pat Hartman]

The problem is a design issue. Your database violates third normal form. You have a flag called available and you are using that flag to populate your selection list. The problem is that as soon as something is selected, it is no longer "available" but you are not updating the flag.

Rather than use a flag which must be updated to reflect a state change, you should be determining availability by comparing the list of selected items with the total list and only returning the not selected ones. Use the query wizard to build an unmatched query. This query will always give you the correct current status and you won't have to rely on a flag that may not be updated at the appropriate time.

 

[tpickles1984]

I think that the "available" field has been misinterpreted here. When a batch of stock is allocated to a harness, this does not mean that it cannot be used again. The idea is that that batches of components can be used multiple times for different harnesses, but when the batch of components is exhausted, this can be denoted by unticking the available field on a seperate form. So the "available" field does not relate to the current problem, but simply relates to the first level of exclusions placed on the selection of components.

The problem is that when a batch of components is allocated to a harness using the form, and the selection form is opened again, this component is still available for allocation to the same harness. If the user tried to allocate this batch of components again, they would get an error message stating that this would create a duplicate value in the "TblHarnessComponents". I would like to exclude from the selection the components that they have already allocated to the harness in question.

I hope I'm making sense with all of this!

 

[Jon K]

In QryAvailable, I added the GI Number field from QryDisplayAllocated, set its criteria to Is Null, and changed the join type to Left Joins. It should now work.

the file is a little to large to upload

You can compact the database before zipping (choose menu Tools, Database Utilities).

 

[Pat Hartman]

you said:

I would like to exclude from the selection the components that they have already allocated to the harness in question.

- I told you how to do that.

you should be determining availability by comparing the list of selected items with the total list and only returning the not selected ones. Use the query wizard to build an unmatched query

 

[tpickles1984]

I'm sorry, I didn't mean to undermine your post in any way. Your indication of where I should be heading was right on the money!

The problem was that I was already thinking along these lines, but hit a stumbling block with the last bit. I'm learning as I go with Access, and I couldn't quite find the code to describe what I wanted.

I'd like to thank you especially Pat; your knowledge of Access seems to be limitless, and your willingness to pass is on to others is greatly appreciated!

Also thanks to Jon K, you seem to have cracked it for me this time. I'll try and implement this in the main database ASAP, I'm sure that it'll be just the thing.