Am I doing this all wrong?

[PeterDuthie]

Please excuse me if I'm being dense here. This is the problem:

Personnel database which is used by various departments each with a table and form recording relevant data. The Main "Workforce" table generates an autonumber which is then used as that person's "ID" and is the main key in the "Workforce" table. How do I get the ID from the "Workforce" table into the other departmental tables - e.g. the "EnvironmentalInduction" table?

At the moment my solution and an SQL query along the lines of:

Dim sqlStr As String

sqlStr = "INSERT INTO [Enviro data] SELECT Workforce.ID AS ID "
sqlStr = sqlStr + "FROM Workforce WHERE NOT EXISTS "
sqlStr = sqlStr + "(Select * from [Enviro data] WHERE "
sqlStr = sqlStr + "[Enviro data].ID = [Workforce].ID);"

DoCmd.RunSQL sqlStr

Which I have in the OnLoad event of the "Enviro data" form. I think I may have missed something. Any help gratefully appreciated.

Cheers
Peter

 

[seaport]

I believe the SQL code is OK.

You did not say what kind of problem you ran into. However, one problem you may have is that you could not run this sql code.

The reason is that when you open the form "Enviro Data", the table "Enviro Data" is locked so that you can not insert records into it.

Try the following codes in the form_load event

me.recordsource=""


sqlStr = "INSERT INTO [Enviro data] SELECT Workforce.ID AS ID "
sqlStr = sqlStr + "FROM Workforce WHERE NOT EXISTS "
sqlStr = sqlStr + "(Select * from [Enviro data] WHERE "
sqlStr = sqlStr + "[Enviro data].ID = [Workforce].ID);"

DoCmd.RunSQL sqlStr
me.recordsource="[enviro data]"
me.requery


Hope this helps.

Seaport

 

[PeterDuthie]

Thanks for your input Seaport. I think I missed out an important piece of information. I'm new to Access and I looked at setting up a one-to-one relationship but I can't see what benefit that would bring me for this particular situation. Am I right about that?

BTW the code does work It's just that I concerned that there may be a more elegant or (better still) more easy way.

Cheers,
Peter

 

[MichaelRed]

Better? that's an open question!

Another "approach" might be to 'seed' all of the 'other' department tables with the Id (autonumber) when the "Workforce" table entry is made. Not really much different, but it could cut down on the load time for the other tables. If you are attempting to maintain you database "normalized" to some degree, this would keep the relationships intact.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[RickSpr]

You have a choice between having separate Workforce and Enviro Data tables in a one-to-one relationship, or combining their columns into a single table. I think you're asking for some advice on choosing.

One-to-one relationships aren't very common, at least in Access, but there are a few reasons you might want to use one. For one, if you have a big table with a lot of columns that aren't used much, you should consider splitting it in two and moving the low-use columns to the auxiliary table. This is especially true on a network. The reason is that, when Access reads a table row across the network, it reads the entire row, then discards the columns that aren't needed for the current query. By splitting the low-use fields out, you avoid useless transfer of data across the network.

Another reason is lock conflict avoidance. If your table's columns are heavily updated by two groups of users who update different sets of columns, then separating the sets into different tables can keep them from locking each other's data. If you're using pessimistic locking, this also avoids contention when one user is only reading the table data.

Still another reason is security. If your table has some columns that should be protected from viewing by some of your users, an easy and safe way to protect them is to put them in a separate table with more limited permissions than the main table. If you leave them in the main table, you have to use logic to protect them (because Access doesn't have field-level permissions), and you have to lock down the user interface so they can't create queries, forms, reports, or modules, nor modify the design of existing ones, and you have to create custom menu bars and toolbars to remove their access to these things. That's a lot of work!

But if the table is of typical size, isn't updated heavily, and has simple security needs, which is most often the case, splitting the table just wastes space. The lost space is tied up in extra indexes for the second table, and in slack space in the extra pages allocated to the second table (because database pages are not shared between tables).
Rick Sprague

 

[PeterDuthie]

Thanks very much Rick and Michael. The two table approach is probably best for this particular case as my client doesn't want the different departments messing with data they should have no interest in.

From what you say it looks like I'm on the right track. The trouble is that I've had to come a long way in two weeks and I haven't had the time to do as much reading as I would have liked: I was worried that I could have missed *the* way of doing this type of database.

Cheers,
Peter