Subform for Multiple Tables

[Guest]

Ok I'm going to try to explain this the best that I can. I have a total of 6
different tables with information. These items that are entered are definitly
going to go thu the "sorting" process, then from there can go to 4 other
possible areas. The user needs to be able to document this all from 1 main
form and update it across the multiple tables. 1 table contains the common
fields between all the tables, we'll call this [Main].

I want in my main form the following to occur:
1) user enters in the unique information for the 1 column headings for
[Main] and auto fills in for the other sub forms with the same column
headings (this I can do).
2) across all sub-forms rows I need to have at least 1 row in every subform
containing the unique number, and also automatically put in a default value.

this is needed because otherwise when I do a querie I cannot see the
information if I have empty fields in a record when generating the report.
therefore in evey table I should have the unique id appear with a default
value of anything (0 would be fine) so I can still generate a querie and
report later on.

 

[Jeff Boyce]

You've described "how", but not "what".

Without a better idea of what data you have stored in your table structure,
it will be a bit difficult to offer specific advice.

Also, we aren't there, so it's tough to guess what you might mean by "go
thru the 'sorting' process".

And it usually isn't necessary to create empty "dummy" rows in your "child"
("many") tables just to see a row in your parent table in a query. Instead,
open the query in design mode and change the join property to show all the
parent table rows, plus any values (including none) from the related child
tables.

 

[Guest]

Sorry, let me try this again. It's hard for me to explain everything. Let me
start with the one table. I work at a metal alloys company, and this database
is for documenting the processes that a metal alloy goes through from being
sorted out to cleaned before being shipped out.

"Sorting Rates" is the one table where information will ALWAYS be entered
into because the alloys are always sorted first. The following colums are
information that must be entered into the system: [UR Lot], [Supplier],
[Alloy], [Processing Time], [Starting Weight], [Starting Date], [Start Time],
[End Date], [End Time], [Material Type], [Remarks], [Operator].

From this process of sorting, the alloy may go through 1 or more of 4
processes. Each one of these processes has it's own table that is identical
to "Sorting Rates", and they are called "Degreasing Rates", "Briquetting
Rates", "Torching Rates", and "Roto-Blasting Rates".

Currently there are 5 different queries (1 for each table). They pull
information from the table to generate a query where an expression is added
to calculate the cost for that process for each alloy. Now, we want 1 query
that can be used to show the cost for all the processes on one report, then
have an expression to show the "total cost" for the alloys handling (which
could be sorting + 1 or more other processes).

At this point I'm not sure if it would be better to use the existing 5
queries to collect all of the information, or to work with the tables for all
the information. I would prefer the tables if possible because this way this
new query would be independent of any search criteria that would be entered
to the existing queries. Any help would be greatly appreciated!!!

You've described "how", but not "what".

Without a better idea of what data you have stored in your table structure,
it will be a bit difficult to offer specific advice.

Also, we aren't there, so it's tough to guess what you might mean by "go
thru the 'sorting' process".

And it usually isn't necessary to create empty "dummy" rows in your "child"
("many") tables just to see a row in your parent table in a query. Instead,
open the query in design mode and change the join property to show all the
parent table rows, plus any values (including none) from the related child
tables.

--
Regards

Jeff Boyce
<Access MVP>

Ok I'm going to try to explain this the best that I can. I have a total of 6
different tables with information. These items that are entered are definitly
going to go thu the "sorting" process, then from there can go to 4 other
possible areas. The user needs to be able to document this all from 1 main
form and update it across the multiple tables. 1 table contains the common
fields between all the tables, we'll call this [Main].

I want in my main form the following to occur:
1) user enters in the unique information for the 1 column headings for
[Main] and auto fills in for the other sub forms with the same column
headings (this I can do).
2) across all sub-forms rows I need to have at least 1 row in every subform
containing the unique number, and also automatically put in a default value.

this is needed because otherwise when I do a querie I cannot see the
information if I have empty fields in a record when generating the report.
therefore in evey table I should have the unique id appear with a default
value of anything (0 would be fine) so I can still generate a querie and
report later on.

 

[Jeff Boyce]

First things first ...

If you have 5 identical tables (structures are the same), and are using the
table names to differentiate data, your database needs further
normalization.

Another approach is to have a single table, identical in structure to the 5,
plus one more field to hold, if I understand, "process type". Now all your
information is available in one place, and if you wish to use 5 queries, the
only difference is which "process" they are selecting on.

Good luck

Jeff Boyce
<Access MVP>

Sorry, let me try this again. It's hard for me to explain everything. Let me
start with the one table. I work at a metal alloys company, and this database
is for documenting the processes that a metal alloy goes through from being
sorted out to cleaned before being shipped out.

"Sorting Rates" is the one table where information will ALWAYS be entered
into because the alloys are always sorted first. The following colums are
information that must be entered into the system: [UR Lot], [Supplier],
[Alloy], [Processing Time], [Starting Weight], [Starting Date], [Start Time],
[End Date], [End Time], [Material Type], [Remarks], [Operator].

From this process of sorting, the alloy may go through 1 or more of 4
processes. Each one of these processes has it's own table that is identical
to "Sorting Rates", and they are called "Degreasing Rates", "Briquetting
Rates", "Torching Rates", and "Roto-Blasting Rates".

Currently there are 5 different queries (1 for each table). They pull
information from the table to generate a query where an expression is added
to calculate the cost for that process for each alloy. Now, we want 1 query
that can be used to show the cost for all the processes on one report, then
have an expression to show the "total cost" for the alloys handling (which
could be sorting + 1 or more other processes).

At this point I'm not sure if it would be better to use the existing 5
queries to collect all of the information, or to work with the tables for all
the information. I would prefer the tables if possible because this way this
new query would be independent of any search criteria that would be entered
to the existing queries. Any help would be greatly appreciated!!!

You've described "how", but not "what".

Without a better idea of what data you have stored in your table structure,
it will be a bit difficult to offer specific advice.

Also, we aren't there, so it's tough to guess what you might mean by "go
thru the 'sorting' process".

And it usually isn't necessary to create empty "dummy" rows in your "child"
("many") tables just to see a row in your parent table in a query. Instead,
open the query in design mode and change the join property to show all the
parent table rows, plus any values (including none) from the related child
tables.

--
Regards

Jeff Boyce
<Access MVP>

Ok I'm going to try to explain this the best that I can. I have a

total of
6

different tables with information. These items that are entered are definitly
going to go thu the "sorting" process, then from there can go to 4 other
possible areas. The user needs to be able to document this all from 1 main
form and update it across the multiple tables. 1 table contains the common
fields between all the tables, we'll call this [Main].

I want in my main form the following to occur:
1) user enters in the unique information for the 1 column headings for
[Main] and auto fills in for the other sub forms with the same column
headings (this I can do).
2) across all sub-forms rows I need to have at least 1 row in every subform
containing the unique number, and also automatically put in a default value.

this is needed because otherwise when I do a querie I cannot see the
information if I have empty fields in a record when generating the report.
therefore in evey table I should have the unique id appear with a default
value of anything (0 would be fine) so I can still generate a querie and
report later on.