Hope this is 'General'

[Hargo]

Hello

I am creating a patient database that has a vast number of Lookup tables which may well need new options adding in the future (these tables all have 2 fields; Code & ??????(whatever the list of options is concerning)

I was hoping to use an update query which asks the user which table they want to update - how would I create this? My intention is to limit users to a switchboard, running the query from a button.

Hopefully Access has a built in object for this as my VBA is almost non-existant

Thanks

 

[plog]

these tables all have 2 fields; Code & ??????(whatever the list of options is concerning

Similliarly structured tables are a sign of improper normalization. What it sounds like is you are storing relevant data in your table names (KidneyCodes, HeartCodes, PancreasCodes). If that's the instance, you only need 1 table for all your codes with a new field to hold the Kidney/Heart/Pancreas data. Again, this is just a sign and maybe your data is properly structured. I'd have to know more about your data to be sure.

I was hoping to use an update query which asks the user which table they want to update

Sign #2--action queries. Users generally don't need to do mass updates on your data (nor should an admin for that matter). Data edits are generally done one at a time via a form. Action queries (UPDATE, DELETE, INSERT) are another sign of improperly structured tables. Can you give an example of how you plan on this UPDATE query being used?

 

[Hargo]

Thanks for prompt reply

Each table has:

a) A code number

b) A list of potential entries for a field, an example being:

Admission Source (where the patient has been admitted from; home, prison, court etc.)


Unfortunately, the code numbers (e.g. 1-8) have been used multiple times so one table could not house them all - I thought about doing it that way first

Essentially, I am having to work with what they already do in Excel but create it in Access. Additionally, they want the data from Access to populate reports in Excel (these are externally sourced so cannot be changed)

I created individual Lookups for each 'topic' so that any updates wouldn't require access to the table if I could create an appropriate update methodology. The updates are as you suggested going to be singular and rare, I was just trying to avoid users delving into the tables.

What I was hoping was that I could pop an 'update table' button on the Switchboard which would ask the user which table - once they selected the table, a dialogue box/form (query) would be opened which would accept the updated data

Thanks Again

 

[plog]

Unfortunately, the code numbers (e.g. 1-8) have been used multiple times so one table could not house them all

That's an incorrect conclusion. Like I said, you differentiate data by fields, not by tables. You would add a new field to tell you what the code represents (Admission, Discharge, Diagnosis, etc.).

Also, if you don't want your user's to interact with your tables, you use forms to allow them to interact with it. That would be my suggestion. Create a form, feed from the table and then they can find the ones they need and can change them. I really think you are overengineering this.

 

[Hargo]

Took your advice (obviously) and I now have one table with all the 'topics' needed for my combo boxes for my data entry form.

I have managed to create the first 5 or 6 and they work fine

The one I am currently working on altered the order of the list from how it appears in my table (made it alphabetical which isn't what I want) so I added the Code field as well this time and used that field to ORDER BY. That did the trick BUT although the entry I want (i.e. the Full MHA Status) goes into the table as required, the form displays the Code only


This is the SQL used for the Row Source:

SELECT [LK_ALL].[MHA Code], [LK_ALL].[Full MHA Status] FROM [LK_ALL] WHERE [LK_ALL].[Full MHA Status] Is Not Null ORDER BY [MHA Code];

Any suggestions much appreciated

 

[plog]

Your last post made very little sense to me. I am completely lost in what you are trying to accomplish and what your issue is.

 

[Hargo]

I've solved it thanks