saving data from a query to a table (1 Viewer)

sh5mg

New member
Local time
Today, 09:06
Joined
Nov 21, 2019
Messages
5
Hi All,


**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).
Best is to 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.

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 T1 Overall item ID) (one 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 wanted 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.

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?
 

Cronk

Registered User.
Local time
Tomorrow, 00:06
Joined
Jul 4, 2013
Messages
2,770
I have been trying to research the difference between "create table query"
and an "append" query
Not much to research. A Create Table Query makes a new table and adds records to that table. An Append Query adds new records to an existing table.


To stop duplicate entries, add a composite index and make it unique. That way only one occurrence of the field combination can exist.


If
Overall goal, sub goal 1, step 1, student 1
....

Overall goal, sub goal 1, step 3, student 2
is to be unique, make a composite index of
OverallGoalID, Subgoal1ID, StepID and StudentID2
 

Users who are viewing this thread

Top Bottom