Master ID across table

[ebiscaro]

To who can help,

I have a master ID [IPWP_ID] that needs to be repeated across multiple tables to be able to create a report with data from al the tables.

Somebody will set up the first entry then I would like for a record with a Master ID entry to be created across 7 different tables. Various perssonell will fill the rest of the record

I am using this lookup

SELECT tbl_IPWP_setup.IPWP_ID, tbl_IPWP_setup.[Maps Number] FROM tbl_IPWP_setup;

but the record is not automatically updated, I need to manually go and choose it.

can you help?

Thank you

 

[Galaxiom]

Wanting to make one ID entry in each of the other tables suggests that the records are related 1:1.
If this is the case then they should be in the same table. Only use a separate table if there are one:many relationships.

Different forms can work with the same table by limiting the fields with the record source query.

 

[ebiscaro]

Thank you. Is it a problem if some of the tables will contain a one to many relationship to another table that needs to be linked to the main ID as well?

Master ID to Table one to one. Table to sub Table one to many

 

[Galaxiom]

That should still be fine. The one:many relationship will still come back to the main ID on the big table.

 

[ebiscaro]

Thank you, now the problem is how do I automatically add the same ID to each many in the subtable?

 

[Galaxiom]

Make the form for your many record tables as a subform of a mainform whose record source is a query based on the main table. The Master/Child links in the subformcontrol properties keep the subform records synchronised to whatever is the current record in the main form. Use the wizard to insert a subform on the main form and this will all be done based on the prompts you answer.

When you make a new record in the subform the foreign key ID as governed by these Master/Child link is automatically inserted into the new record.

Hope this makes sense.

 

[ebiscaro]

it does, thank you

 

[ebiscaro]

Now the problem is that my table is larger than 255 rows...should I split the tables? Is there a way to have more rows?

Thank you

Enrico

 

[Galaxiom]

Now the problem is that my table is larger than 255 rows...should I split the tables? Is there a way to have more rows?

This would mean you need to restructure the data. Some of those fields will have similarities and should be taken off into another table where the particular property is designated in one field and the value in another.

For example you would not have a column for each day of the week but another table with the value recorded against the day of the week. Many more records in another table but fewer fields overall.

Look for similarities in the fields and combine them. Access works much better with many records and few fields.

Tell us more about your data and we could suggest the kinds of fields you could combine into another table.

 

[ebiscaro]

I understand.
Many record are a simple yes/no type. This is almost like a check list where different disciplines need to have their input before we perform a job.
The other fields are either data from previous job for reference or fluid properties or engineering calculation results.

hope this makes sense

 

[Galaxiom]

Set up your checklists as a table with MasterID and DisciplineID.
DisciplineID is a code for the Discipline and these codes are recorded in a table with ID and DisciplineName. Whereever you refer to the Discipline in other records you use its ID.

Add a Discipline record against the MasterID as required. You don't need Y/N because the presence or absence of a record indicates this.

You will find it much easier to query the disciplines this way. Simply list the records for the particular MasterID. Or get a list of MasterIDs with a particular discipline. Recorded in separate fields you would have to query every field to get this information.

Similarly with the engineering calculation results. These will be just numbers and can be stored in a table. You might also have a field for the units.

 

[The_Doc_Man]

In general, when you have over 255 fields in a single record you have mis-designed your table. You SERIOUSLY need to study "database normalization" before working too much more with this database. Right now you are thinking flat-file, which is Excel.

I will not go so far as to say there is no way in Hell that your design is right; it could be. But if it is, you have reached a situation where Access is the wrong tool for the job. Based on our past experience here on the forum, 99% of the time when you say you need 255 or more fields in a single table, the truth is the table isn't properly normalized.

Study normalization and browse through the "Theory and Design" heading of this forum for articles on how to identify what needs to be in which tables.