using 1 form to create multiple records (1 Viewer)

Hello!

I have a database that goes sorta like this:
Table 1
Overall item ID (PK)
{general info about the overall item ID}

Table 2
Overall Item ID (not a PK) (linked to T1 Overall item ID) (one to many)
subset 1
{general info about subset 1}

Table 3
Overall Item ID (not a PK) (linked to T2 Overall item ID) (many to many)
subset 1 (not a PK) (linked to T2 subset 1) (one to many)
subset 2
{general info about subset 2}

I use forms to have people enter the data. forms go as follows
F1 - Enters all data for Table 1

F2- references ID (and necessary other data) from F1, and uses a subform (in a table manor) to enter all data into Table 2

F3- references ID (and other necessary data) from F2 and contains a subform to enter info regarding subset 2. Additionally, there are buttons to cycle between the records to be able to access the other lines of subset 1 to enter their subset 2 data.

as a result, the data table 3 looks something like this:
Overall subset 1 subset 2
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3 .... and so on until it cycles through all overalls, subset 1 and subset 2.

I want to create a 4th form to enter a last subset. I want people to be able to use a table-style subform to create up to 7 rows, but have that data "duplicate" the existing rows (into a new table) based on the number selected. without having to enter it more than once.

ie, i wanna be able to select
A
B

and have it pop out like
Overall subset 1 subset 2 Subset 3
1 1 1 A
1 1 1 B
1 1 2 A
1 1 2 B
1 1 3 A
1 1 3 B..... and so on.

so have A and B apply to all records, after only being entered once.

any and all ideas are appreciated.

**pls note- the data is proprietary so it cannot be actually shared. The other forms and tables are relatively set, as it is something i inherited. I am really just hoping to find out if this is possible.
Please feel free to ask clarification questions, but all data will be described as "1 2 3" or "a b c" to avoid me getting into any trouble (sorry for the inconvenience).

Hi. Almost anything is possible using VBA. But whether it's a good idea or not, is another matter. What part, specifically, do you need help with?

**pls note- the data is proprietary so it cannot be actually shared

Click to expand...

Data is one thing, tables and fields are another. You've made your situation too generic to be understandable. Can you set up the Relationship tool and post a screen shot of the tables in question?

Or, provide something analogous--perhaps an educational system. There are school districts which have multiple campuses which have multiple buildings which have multiple rooms, etc. etc. Give us something we can get our minds around.

OK, you have this, where PK = Prime Key, FK = Foreign Key, mCK = member of compound key


I did this with a couple of corrections.

First, you CAN'T link the ItemID back to Table 2 because it is not a PK in that table - but in that table, the ItemID as an FK can link to T1, so here the FK can ALSO link to T1. I.e. when you appear to have a transitive relationship, link to the far end of the relationship, not the near end. Faster and less trouble in the long run.

Second, I pointed out that for efficiency, you could/should declare a compound key (a key made up of more than one field) as the primary key of T2 and T3. T1's PK is fine.

I want to create a 4th form to enter a last subset. I want people to be able to use a table-style subform to create up to 7 rows, but have that data "duplicate" the existing rows (into a new table) based on the number selected. without having to enter it more than once.

ie, i wanna be able to select
A
B

and have it pop out like
Overall subset 1 subset 2 Subset 3
1 1 1 A
1 1 1 B
1 1 2 A
1 1 2 B
1 1 3 A
1 1 3 B..... and so on.

so have A and B apply to all records, after only being entered once.

Click to expand...

My first question is this: You are talking about combinations. Is it the case that whatever your table looks like, you intend to have records for every possible combination of available values? That is, let's say you have 3 items and 4 1st-level codes and 5 2nd-level codes, that will be 3x4x5 = 60 records at 3rd level (and 12 at 2nd level.) Is that what you wanted?

And if you have two selections at your 3rd level, you would end up with 2x3xx5=120 records at third level. I know you probably have other numbers than this, but is this the direction you are going?

If so, you want to look up the concept of the Cartesian JOIN as a way to generate the combinations via a tricky little query. BUT if you are not expecting to have all possible combinations, then we are talking something else. We need some details here.

First off, thank you all for all your help!

PLOG- (unfortunately, table/field names are dead giveaways) think of it as something at school, Some Overall Giant Overarching goal, this breaks down into sub goals (that will be used to accomplish the main goal), and then the sub goal breaks down into the steps to accomplish it. The last step is basically saying which students you want to apply it to.

(to clarify on The Doc Mans post) - yes so as a result if I have 1 goal, 2 sub goals, 3 steps and 2 students, the final result is
1 x 2 x 3 x 2 = 12 results in the end.


I have figured out that if i use a separate table, that only contains Overarching Goal # and the names of the students, and run a query that pulls this and Table 3, it will give me the results i want.
ie
Overall goal, sub goal 1, step 1, student 1
Overall goal, sub goal 1, step 2, student 1
Overall goal, sub goal 1, step 3, student 1
Overall goal, sub goal 1, step 1, student 2
Overall goal, sub goal 1, step 2, student 2
Overall goal, sub goal 1, step 3, student 2
and so on....


Now i am wondering about saving this to a table.....
I have been trying to research the difference between "create table query"
and an "append" query

the issue- new data will be added constantly, so the query will have to be rerun often,
but i dont want to have duplicates. (ie i dont want to have the break down of Little johnny learning how to read in the table 9 times since ive run the query 9 times)

I also want to use forms to update the data in the table (ie lets say the status of the steps being completed by the student) and have it save properly to the table and not have the query affect it (ie when i first run the query, Lil Johnny is at 0% when learning to read. then i update the table with a form so it changes to 50%. I dont want the query to 1) change the 50% to 0% or 2) create 2 lines, one with Lil johnny at 50% and another at 0%)

any suggestions?