Append querys, Many-to-one relationships and forms, oh my!

[Newbie!]

Not sure if this is more of a form question or a table one, but anyway...

In short I'm trying to make a system allowing me to append data in one form to another when the form contains subforms (actually I am trying to do this with several similar forms but lets not confuse things). The problem is each form has data from two tables with a many to one relationship because... for every item in my first table I want to have a variable number of other items. I will use a fictitious example to illustrate what I mean (or confuse people more)

e.g.
John Johnson's hobbies are: 1. Tenis 2. Knitting 3. Hola hopping
Bob Bobson's hobbies are 1. Staring out of windows 2. Go Karting
Eric Bobson's hobbies are... etc....

So in this fictional example I will have 2 forms near identical forms called, 'people I want to hang out with' and 'people who are tall'. Each form is made out of a data from two tables, one for names and one for hobbies. So 2 forms = 4 tables
Now I want to copy data from the 'people who are tall' form including the related data to the 'people I want to hang out with' form in a way that preserves the relationship one to many relationship so when I append the names in one table it will append the hobbies in another.

My attempts to do it so far have failed on the table level and I'm not sure what to do? I think this may be in part because the foreign key will change when you append data and sever the link and also querries from what I can see can use data from multiple tables but only to append to a single table rather than two
I wonder whether (in this example) a way round this would be to keep hobbies for both forms in one table.... but I also think I read on this forum to avoid these table relatioships and use subforms instead which I didnt realise was possible

Anyway if someone can give me pointers I would be over the moon

(I havent even got onto the issue of making a pretty UI for my collegues to append with a single stroke of a button!)

 

[theDBguy]

Have you seen/tried this?

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

 

[Cronk]

I hope when you say you have 4 tables, that you don't mean you have four physical tables.

'people I want to hang out with' and 'people who are tall' are two properties for the people entity so you would have 2 fields, one for each of these in the people table. Then you could have one form to show people and a subform showing their hobbies. Then you open the one form with a filter applied for the tall people, for the hang-out people or no filter to show the lot.

 

[strive4peace]

hi Newbie,

Welcome! You can find an example of many-many relationship between Contacts and Lists, which uses a cross-reference table called Members, in this free download:

My Contacts
http://msaccessgurus.com/tool/MyContacts.htm

here is an article about that:

Cross-referencing Contacts and Lists
https://sfmagazine.com/post-entry/december-2018-access-cross-reference-contacts-and-lists/

 

[Newbie!]

Oh wow, thats a lot different options! I feel like a kid in a candy store (or however the analogy goes)

@theDBguy
Nah, havent seen anything like this I have been using guides like these so far, https://www.guru99.com/ms-access-tutorial.html#13 https://www.tutorialspoint.com/ms_access/ms_access_quick_guide.htm
I have made limited use of VBA and no SQL. Am I correct in saying that my database will be rather limited unless I can master these languages?

This Allen Browne seems awesome, may have to explore his site in general somemore!
If I am correct his way round this issue is to copy the data from the form (and thus from the first table) without using a querry, use the new key create to link to subform (and thus table 2) and yeah, generally other magic

@Cronk
I most certainly have 4 physical tables.... in fact given there are 5 forms I have 10 physical tables! I mean isn't there a saying 'the more the merrier'!
I Figured it may be the wrong way but best to learn from mistakes etc, and that's the way most online guides teach you, tables first then forms. In fact the 5 forms vary a lot, (I really need to get better at coming up with examples), as they are all unique processes. But.... say 10 of the fields are the same no matter what, and the data from the sub forms will nearly always match.
Better example, I run a (fictional) wedding planner service so the Bride and Groom's will details will go into one table along with other things like cake, etc, while the guests will go into a separate table as some couples have no guests and some have 100s. All goes together nicely in one form using the guest data as a sub form. It also so happens my company does pony trekking too and the couples who love my weddings sometimes want to go pony treking with all or most their guests.... and just for good measure I also run medical research and people who know I'm a great person from pony trekking or weddings love becoming test subjects.... with all their guests!
So I want to be able to copy the couples and guests details into other forms if needed as typing it all out again will take ages. My question for you then.... is there a better way of laying out my data which I am missing? I was considering if 'guests' for all activities could be on a single table that the other tables just pull from.

@strive4peace

Interesting! So is this a ready made database that you can take apart and see how it works?

 

[strive4peace]

Interesting! So is this a ready made database that you can take apart and see how it works?

yes! It is made to help others learn Here are a couple more resources to help you master the mighty power of Access:

Access Basics
http://www.accessmvp.com/strive4peace
Free 100-page book that covers essentials in Access

Learn VBA
http://www.accessmvp.com/strive4peace/VBA.htm

and Allen Browne's site is wonderful!

 

[Cronk]

#Newbie,

I mean isn't there a saying 'the more the merrier'!

A developer soon learns that is 'the more there is, the more there is to maintain/update'

In your wedding planning database, I would have one table for Guests, with a field indicating type of Guest eg bride, groom, celebrant, BrideGuest, etc. They are all people, just having a different name or phone number or whatever. Just the same as if you have different colored objects. One table with a color field, not a table of red balls, another for blue balls etc

An experienced developer will try to minimize the number of tables, queries, forms, reports and code procedures. Instead of having 2 forms, one displaying some fields, the other not displaying those fields, then instead have one form with code used to display/hide fields.

The same data duplicated across multiple tables means more work in data maintenance and risks to data integrity.

 

[Pat Hartman]

Forms don't store data. Tables store data so you might want to think about this differently. I'm working on a similar process. I have a complex insurance app that takes census files (people to be insured) and rates them so that my client knows what to charge for the insurance. All this process takes place outside of the main application which handles policies once they are in force. So, at some point, the user says "approve" and i have to run code and queries to examine all the records and make sure that all required fields are present. Due to the flexibility of the import process I can only make one field required and that is the Employee's ID from his employer so that we have a way of matching existing files with other information and updating records already added. Data that is input must be valid in context. Dates must be dates. Status values must be valid, etc. but all but the one field can be empty.

The move process is complicated because there are numerous source tables and numerous target tables and they are not one-to-one so most of the work is done using code similar to that in Alan's example. You have to append the "parent" record in order to get the foreign key you need so you can import the "child" record so with two tables, you have two loops. The outer one that controls the parent records and then an inner loop that controls the children for each parent. The easiest way to do this is with the code you've already been given. To give you more specific advice, we would need to know your schema and exactly what is being copied where.

I can also offer a very little code option. In this option, you would need to add a new field to the parent table and when you append the records to the parent table, you map your source table pk to the new field in the parent table. Then your append query for the child table joins to the target parent table on FK to the new field (which is the old parent's PK) and picks the parent key from the target parent as the FK to insert into the target child table rather than the field from the source child table.

You might have to think about that slowly and map it out on paper. In the cases where I have used this technique, I generally run an update query Before the fact to set all the "new" field values back to null so there is no confusion with existing data. I do this "before" rather than "after" because it is helpful when testing to be able to see how your query is working and if you erase the results, you won't have a good way to validate that everything got moved.

The bottom line is - parent tables (1-side) must get added before the child tables (many-side) so you could run several 1-side append queries and then follow with the many-side append queries.

Don't forget, your queries will need selection criteria since you are not appending all records. But do stop thinking about this as a form problem.

 

[Newbie!]

Sorry I'm late to reply folks, I have had to make a couple of day long, scary, but sadly necessary, adventures outside the house during this crisis which took the puff out my sails! I'm safley home so back to database making (after oversleeping like a person who likes sleeping way too much)!

@strive4peace
Well, I think that solves my problem of trying to find a site to learn off!

@Cronk
I see what you mean, and my examples are terrible
Maybe if I made it a wedding for pets instead?
Whatever I sadly have to keep the data in the first table seperate from the second table because they have very are very different things and are used differently in the process.
Its difficult to explain what the work is, but in a nutshell I have 5 or 6 seperate processes and sometimes sizable chunks of data from one process will need to be duplicated in another, so a lot of time is wasted typing the same info out again which is one reason I'm trying to build a database. So far I'm stuck using 2 tables per process as each case in a process can have a variable number of sub cases and I don't know of any other way to deal with this variable issue. In the example I used for one wedding I could have 1 guest, I may have 10001! In any case its data from both tables which is needed to make up my pretty UI in the form of a form. Oh and I havent even got onto reports yet.... I was going to 40 different template varients that are automatically selected based on the information given in form per row :O. I mean of course I have to have a very different invite based on the grooms starsign
.....Yeah, this project could end up rather painful! I do appreciate what you are saying though the more complicated the database the harder it will make life and ultimatly the reason for doing this is to make a process which is easier for the user than just storing the data in lots of excel spreadsheets

@Pat Hartman
Oh wow, that does seem like a mammoth task! Thanks for clarifing the forms/table issue, in terms of the form my main issue here is really making a UI that is capable of activating the copy or appending process as I don't want my collegues playing around in tables, but thats an issue for later I think

My scheme hasnt been mapped out fully, simply as I am testing concepts like this one on test databases bases first. What I have mapped out though is that the parent table for each of the 5-6 processes will contain a variety of fields but I only need to be able to append or copy 13 short text fields. In the child table likewise there will be other information but only 9 short text fields I want to copy. There is no reason not to lay out the short text fields beside each other in the table which should help.

So for the test if I keep it to two process each made out of 2 tables.

First process I will call Orignal process formed out of 2 tables, OP (original parent) and OC (original child)
OP has field OPKey as an autokey and then PData (as in Parent Data) going PData1 - PData 13 as short text
OC has OCKey as an auto key then OPKey as a number field (foreign key) then CData1 - CData9 as short text

Second process I will call New process and has a similar format i.e.
2 tables, NP (New parent) and NC (New child)
NP has field NPKey as an autokey and then PData (as in Parent Data) going PData1 - PData 13 as short text
NC has NCKey as an auto key then NPKey as a number field (foreign key) then CData1 - CData9 as short text

I have done the relationships for both processes, not set up any forms yet

Hows that for a set up?


I have thought of an alternative as an alternative I could find a way to select a row worth of data, copy, then paste manually to another location, but Access doenst work like Excel

 

[Pat Hartman]

You are thinking like you would if you were working with a spreadsheet. Take off your spreadsheet hat and put on your relational database hat. You will almost certainly be using append queries to do this process and that is two lines of code. run the append the parent table query. run the append the child table query. The missing link is how do you get the "new" PK from the target parent table so you can use it as the FK in the append the child table query and I told you how to do that. Just take a few minutes to work through the logic of how you will use a join to get the value you need. If you use the alternate, no code method, this actually becomes almost trivial.

 

[Newbie!]

Ok I'm trying the low code method as I don't yet know SQL... yet

Originally posted for help as I got stuck again, but deleted it as I got unstuck, only to get stuck again
Its the cycle of self teaching!

@Pat Hartman
Ok I worked out what a join was though this site https://www.tutorialspoint.com/ms_access/ms_access_joins.htm (I know I really do live up to my name)
but now not quite sure what I am doing. I have included a picuture below of my second querry

As before I want to append OC to NC, but I have now done what I think is an inner join between OPkey (original parent key) and Newfield

Its possible I have not done the link correctly, but how will the OPkey connect to Newfield, take the NPkey as its ownwhen it inserts. I tried Self Joins but I'm not sure thats it. Or am I overthinking this and I need to append the NPKey from NP to NP Key in NC while appending CDate1 etc to NC?

Regardless it seems to say its transfering no records and I wonder if as you have said, I need to reset certain values to zero