Populating fields in one table with data from another table

[KarlosMacronius]

Hello, I'm new to access and I realise that this may be an incredibly basic question, but...

I am trying to populate fields of one table from another table.

I have a number of hand written forms to digitise by putting them onto a database.

We have a register lists and a detailed information sheet for each item listed.
Some info from the register lists is duplicated on the info sheet. So what seems the obvious thing to do is have the duplicate items self populate.

the tables go like this:
REGISTER
project number
project name
context number (always a unique ID)
brief description
number taken out by

INFO SHEET
project number
project name
context number (unique ID)
type
full description
photo number
sheet filled out by
etc.

PHOTO REGISTER
project number
project name
photo number (unique ID)
context number (can be several in one photo)
description
photo taken by
etc.

Once the registers are entered into their tables I want all matching data for one context number to be duplicated on the info sheet table so the data entrant can cross reference it and spot any errors on the hard copy.

I have simple forms for each of the different bits of paper (register, info sheet, photo register) to make digitising easier and avoid people touching my precious tables. But I'm having trouble actually getting these different tables to auto fill each other. (though I would also like any relevant information already entered into any table to appear on the info sheet form when entering data, but I think this might happen automatically)

It's a bit of an oddball use as its not the typical sales/stock/customer database. perhaps I'm not getting the answer I want because I cant quite relate these commonly used examples to my own use.

I'd appreciate any help anyone might be able to offer and apologise for asking what is probably a very very basic question.

Thanks!

[twinnyfo]

Karol’s,

Welcome to Bytes!

Your description is a bit confusing. But it seems to me that if you have all this data that simply pertains to each item, then why not put all that data into one table? The only secondary table I see a need for is the Photo Register Context number, as there can be multiples for each item.

Don’t worry about people touching your tables—if you build your DB properly, they will never see them—nor will they ever have a need to know what they are.

We might need more information on this, though, as your description is a bit scant.

Standing by to provide more hepp if you need it.

[KarlosMacronius]

Thanks for the speedy reply.

Sorry for the confusing explanation, I'll try and simplify it.

There are two main purposes to our database, one is to preserve an electronic copy of hard documents as they are in real life, and the second is to manipulate/interrogate the data.

At this point we cant combine the data into one table because we need a digital version of each piece of paper we have for archiving purposes.

We have three pieces of paper to put into our database, currently they are all entered directly into their own tables:

The item register listing all contexts (used in hard copy as an index for the info sheets)

Then we have the info sheet which shares some details from the register as well as going into far more detail about a context.

Then the photo register lists all the photos and what they are of.

This leaves me with 3 tables, some of which have the same information in them. (context number for example) All three are entered at different times by different people (it would not be practical to enter all the information for one context at once.)

So once I've entered the project code and name for one context into the register table, I would like it to appear in the relevant field in the other tables (simply to reduce data entry time).

Or would it be better/possible to produce a set of forms for staff to fill in for each of the pieces of paper that would put the relevant information into the relevant tables?

I'd like to keep the tables separate as when everything is combined it makes it difficult to work with (I've only mentioned three different tables for brevity but in reality there are far more)


I hope that makes a bit more sense but I fear it wont.

[twinnyfo]

Karlos:
At this point we cant combine the data into one table because we need a digital version of each piece of paper we have for archiving purposes.

This has nothing to do with maintain all data pertaining to one record in one table.

Karlos:
This leaves me with 3 tables, some of which have the same information in them. (context number for example) All three are entered at different times by different people (it would not be practical to enter all the information for one context at once.)

I get the impression that you are hesitant to combine these tables into one because multiple people work on them?

Karlos:
I'd like to keep the tables separate as when everything is combined it makes it difficult to work with (I've only mentioned three different tables for brevity but in reality there are far more)

In my experience, more tables make it more difficult to manage. I am all for having many tables, when there is a need for many tables. However, I still am confused about your table structure.

If you have one table with the majority of the data (and you may still need two more tables) then the people that work on one part of the data can update the fields they need to update and those who update other data update other fields. The photos table should simple list the photos and the context table, just the contexts. This is all based on a limited knowledge of your DB and its structure and the exact data you are entering.

Keep in mind, that even though you are “describing” your DB, you are using the jargon that is familiar to your community and its uses. We have n idea what a “context” is within this context—pun intended.

I think we still need more information before we can structure your tables properly.

[Rabbit]

Information from a higher level should not be repeated at a lower level. For example, your register/project level information such as project name and context number should not be repeated in all lower level tables. They can be displayed by joining to the higher level table. But there's no need to repeatedly store and enter it multiple times.

The lower level tables should only contain the unique identifier from the higher level table. In this case, most likely the project id.