how to create a form with multiple tables that are linked together

[knix]

I have this access project consisting of multiple tables that are linked together in a relationship. I would like to migrate the consolidated information through appending in a datasheet form or importing the gathered information . Can anyone please give solutions with my problem?

Can I make a datasheet form (consisting fields from multiple tables that are linked together with relationships) in ms access which I could paste append or insert data? I have tried it but the form was not capable of adding or even pasting data because of the referencial integrity. Can anyone help me?

OR

I'm trying to import a single excel file (worksheet) into an access file with multiple tables that are linked together with relationships (1-many, 1-1, etc.). Any way to do this without having a temp table and an update query?

Thanks in advance.

[NeoPa]

I tried reading your question through a number of times, but I kept finding myself half way through, realising I was again unable to follow what you were talking about.

I took a step back and realised there are very few actual sentences, but just a bunch of words following on from each other.

I suggest, if you want any help, you phrase the question in real sentences that make sense.

[knix]

I tried reading your question through a number of times, but I kept finding myself half way through, realising I was again unable to follow what you were talking about.

I took a step back and realised there are very few actual sentences, but just a bunch of words following on from each other.

I suggest, if you want any help, you phrase the question in real sentences that make sense.

Okay to make it simpler, I will make a profiling system out of MS Access. I have these tables:

FM Personal Information

FM Employment Info

FM Education

FM Trainings

FM Professional Info

FM Citations

FM Research and Publications

FM Load – handled subjects

In the free online survey that I have published, the above information/details were gathered all together, resulting to less than 100 columns per record. Now, my problem is how to migrate the collected data faster in my normalized tables.

I tried to create a form in a datasheet layout, however, I was unable to paste or even insert data because of the referential integrity. Are there any possible means to do this?

Please help. Thanks.

---------FM means faculty member

[NeoPa]

I would like to help, but I still don't have much of an understanding of what you're trying to do or having difficulty with.

Somewhere in the overall picture is some sort of data source with 100 columns. I would guess that you need to apportion that data sensibly among the various tables mentioned :S Not too sure on that bit even.

This is really anything I can help with at the moment as I don't even have a rudimentary understanding of where you're coming from (I know it's related to a survey but I'm not sure if/how that helps).

Try asking the question as if it were a technical question. Forget ordinary language. Consider "What would he need to know about if he's going to help?"

PS. Sorry for the relatively long delay. I kept visiting the thread in the hope of being able to make more sense of it.

[NeoPa]

Rather irritatingly, I find that your later post is some sort of clarification of the earlier one, rather than a sensibly phrased explanation in it's own right. This meant I had to reread through the nonsense (literally) that was the first post.

Having gone to that extra trouble now, I think I may have a rudimentary understanding of your question so I will post what I can to help.

Firstly, having a form that can update the underlying data depends on the underlying recordset being updatable. This gets very difficult when more than a couple of tables are included. This may be possible (in some circumstances) but I don't hold out much hope for finding it without help (which would obviously need all of the information precisely posted - a big ask).

Second point. An Access database can link to a spreadsheet file and show the contents as a table, usable by Access. This can be useful in situations like this (possibly avoiding the need for a temp table).

One-to-One links may prove difficult, especially with full referential integrity set, but otherwise the data can be added, table by table, from the Excel table to all of the FM tables. I'm not sure if it's even possible to append to queries rather than simply to tables, but even if it is I have never done it. Otherwise each table must be appended to in order. The order must be such that those with no dependencies are done first and any table with dependencies must be done AFTER any dependent tables.

I hope this helps. This is the best I can do from my limited understanding of your problem.