Filtering a query

[mudstone]

I have just made the transition from Excel to Access. I have created the tables for my database. The database includes two tables:

1. Mining
2. Grade Control

I have created a query to generate a calculated field called BlockID. This field is populated with the concatenation of three fields in the Grade Control table.

BlockID: ([Material_Code] & "_" & [Block_Number] & "_" & [Mining_Flitch])

The Mining table also has a BlockID field and when selected a combo box appears containing the query result of the concatenation described above of each record in the Grade Control table. However, the combo list contains duplicate records but with different Pit_Project areas. The combo box needs to be filtered by the Pit_Project field which is common to both tables.

So when the BlockID combo box is selected in the Mining table a list of options are presented that have the same Pit_Project name as the selected record in the Mining table. "

Any help is appreciated

 

[jjturner]

Hello and Welcome!

And let me congratulate you on your move from Excel to Access

To address your issue, I'm assuming you're using a form to populate your Mining table. (But if the combo you speak of is in the table itself, I recommend in the strongest terms to get rid of it - don't do it! - otherwise you'll discover how evil this can be )

So continuing with the assumption that you have a form bound to your Mining table, you should be able to simply add a criteria to your query which builds the BlockID:

In the Pit_Project field in your query (which you should add if it isn't yet in the design grid), add the criteria "[Forms]![frmMyMiningForm]![txtPitProject]" (minus the quotes) where "txtPitProject" is the name of the control which holds the field value of Pit_Project in your Mining table.

Hope this helps.

Regards,
John

 

[mudstone]

Thanks John
I have managed to get the query working OK from data stored in the "Grade Control" table. The fields concatenate in the correct format, to produce the BlockID field, and filtered. However, I have just discovered that I cannot use this field in the query as a primary key field (one too many). I am I missing something??

I would like to do the following;
I would like to have the BlockID field in the "Grade Control" table which was the original design. Is there any way of having the data (concatenation of four fields in the same table) written to this field as each record is created. This would solve my main problem but if you can suggest something easier please do.

Regards

 

[jjturner]

Hi mudstone,

Something I overlooked in your original post but which your most recent response calls to attention:
One of the basic principles of database design is to 'never' store calculated values in your tables.

If some series of fields is being used to generate a "composite" value (i.e., BlockID), then that "composite" value is being derived from them. In other words, it should only be generated at 'run-time' from a query and not stored/saved into your tables.

Secondly, by setting your BlockID field as the Primary Key, you're actually violating another fundamental principle of database design:
The Primary Key should not represent some encoded 'meaning' - it should be meaningless except to function as a unique identifier for the records in your table - a reference point and nothing more.

I'm not trying to sound harsh - I'm just trying to point you in the right direction with your design (and hopefully save you from some of the many missteps that I've made along the way to learning databases).

So that said, it sounds like we need to get your tables sorted. I would recommend taking this topic up in a new thread under the "Tables" forum.
There I'd list your complete schema - tables and fields (with data type) - and relationships (primary keys, foreign keys).

HTH,
John