Combining Fileds from Different tables (Access 97)

Topic

New Reply

I’m designing a database to hold data on objections to a local plan. I will have a table for the objectors, a table for the objections (there can be many per objector) and a table (or filed in the objections table) relating to the plan stage. I will need to take the autonumber ID field from the Objector table and the Autonumber ID field from the Objections table along with the field for the plan stage (text field) and combine them to create a unique number for each objection. Each objector can make many objections but each objection can only be made my one objector so there is a one to many relationship between the two tables.

I believe I need to use a query with some form of concatenated format, any help on this would be appreciated.

Reply | Quote

Replies

I’m confused here. You already have an autonumber for each objection, which by definition is unique. Why are you trying to create another ID number?

Reply | Quote

My need for another ID is not so that it becomes a link to any table it is purely so that each objection has a number that combines the objector number and the plan stage. Each paper objection received by the department must be filed and the staff want to be able to give each one a number that resembles the above. The number generated will appear on the form but may not appear in the table itself because I can already, as you pointed out, use the two IDs from the two tables in any query to run any reports etc. I am purely acting on what my clients want to see in the database and have seen it on several example screen shots that they have provided me from other databases.

Reply | Quote

If you aren’t storing the number, then what good is it going to be? If a record is deleted for some reason and then reentered, the numbers will be different in the computer so they will no longer bear any relation to the “number” your clients have used. Does that matter? I’d suggest that if you are going to create a value like that, you need to store it in the record somewhere. In fact, you *must* store it in order to be able to look it up and to insure that it is unique. Compacting a database can reset autonumbers for records that have been deleted from the end of the tables. That means there is a theoretical possibility that the same numbers could be generated more than once.

Clients often insist on something that isn’t good for them and will frequently come back with printouts from flat files and spreadsheets to show that another product can do it. That only means that they’ve made up their minds about how they want it done OR someone has convinced them that’s the way to do it. Your job is to figure out a better way because the client is *not* always right (unfortunately), but they *are* the ones who pay you. That means it’s up to you to diplomatically sway them to something reasonable or explain how much more it will cost to do it their way and how prone to breakage it will be. That works better if you have a viable alternative to offer them, of course.

Reply | Quote

What is the relationship between objections and plan stages? That is, is each objection unique to a plan stage, or can a single objection be used for more than 1 plan stage.

If the former, then the plan ID is superfluous for ID purposes. You can always print it anywhere you want, but objection #12345 is unique. Making it #12345-1 doesn’t help you, because there is never a -2.

If an objection can cover more than 1 stage, however, this is different. You then need another table, a child table to the objection table. It lists each plan that is being objected to by the Objection #12345. Then #12345-1 means something specific.

Reply | Quote

The plan stage is relevant to the time the objection was made and an objection can be carried on into further different plan stages. An objection can be made at any stage of the plan and each one will be indicated as being made at a particular stage. I’ve received a fix for my query from Stewart, thanks for your involvement also.

Reply | Quote

What help in particular are you looking for? Table Design, Query?

Reply | Quote

I’m looking for query design help to combine two or more fields from more than 1 table to provide the users in the Planning Unit with a number they can use as they have requested.

Reply | Quote

You can build an expression in a query. Use the syntax

ObjectionNumber: [ObjectorID] & “-” & [ObjectionID] & “/” & [PlanField]

where objectorID = 10, ObjectionID = 0001 and PlanField = AZ324 this expression would return
10-0001/AZ324

or

ObjectionNumber: [ObjectorID] & [ObjectionID] & [PlanField]

where objectorID = 10, ObjectionID = 0001 and PlanField = AZ324 this expression would return
100001AZ324

The expression wil have the name/label “ObjectionNumber”. The colon ends the naiming section.

Each field that you want can be concatenated by using the ampersand “&” between fields. To add formating characters between fields (if required) use the ampersand with the character emclosed in double quotes.

Reply | Quote

Stewart, this was exactly what I was after a simple answer, I knew somehow it could be done, I’ve had some others reply to my question but all have been asking me why I am doing it you gave me the answer. Thanks.

Reply | Quote

Stewart, I have another quick question regarding the concatenation of fields that you kindly gave me a solution for. I have created the query that combines the fields from three tables and this works great, what I now need to do is simply show this on the form, I don’t need to record this as data in a table but I will need to use it on a report. The report will be listing all representations logged under each person and each representation will need to show the combined number. Do you think I ought to record this number in a field in the representations table or not? You help on displaying this number on the form would be most useful I’ve tried doing this using the On Current element of the form and typing code to show the concatenation but this didn’t work. Any ideas? Paul

Reply | Quote

Paul,

Ideally the datasource for the report & or form would be the query that you created that contains the concatenated expression. Alternately if this is not practical consider modifying the datasource that you are using to include the concatenated expression so it is displayed in conjunction with each record.

Using the on current event could populate an unbound field fairly readily but that assumes that either the required fields are present in the form or the expression is build in code from the related tables.

Including the expression in the query the form is based on is probably the simplest solution.

Stewart

Reply | Quote