Multiple Table Entries from One Form (1 Viewer)

I am a little stumped on this one, have been working with Access for about 6 months now, and one of my projects has grown to a point where I need some help.

I have a two tables, one is a "static" table of names. The other is a table of assignments. In my static table, there is about 100 names. I am trying to create a form that will allow me to select multiple names/entries from the static table and assign them an assignment. The trick is, I need to create a separate entry in the assignment table for each name selected.

The form now works as long as I select each name individually and create their assignment. The problem is, now that the list grows, it would make it much easier to select from a list and create 40-50 assignments with one click instead of 40-50 times.

I need to track them in individual rows so that I can track completion separately. If I do the multi-selection to one table entry, when I check my completed yes/no field, it will mark complete for everyone in that row/entry.

Thanks for the help, and I appreciate it!

Respectfully,
Will

It sounds like you have 3 base tables.

Person
Task
PersonAssignedToTask

Here is a typical model of these tables.
It may offer a starting point. You'll need to assign other attributes like TaskAssignedDate
TaskStartDate, TaskCompletionDate, you could have Expected and Actual etc.

A more thorough description of your 40-50 assignments etc would be helpful to readers.

You may get some insight from links on this page.

Good luck.

Once you have it set up in the fashion explained by jdraw, your next step would be to devise a way of selecting the individuals, possibly a multi select listbox.

You would then need a way to select an assignment. This could be accomplished with a combo box.

The next stage would be some VBA code that would look through the listbox identifying the selected items, finding the person's ID number then adding that ID, along with the assignment number to the Person Assigned to Task Table.





Sent from my SM-G925F using Tapatalk

I understand, and thanks for the help. The tables have many fields, but I just wanted to keep it simple so that I could get the point across. The tasks are dynamic and they don't have a table. Instead, the name of the task is a field in the assignment table. That table contains, unique_id, assignment_name, date_assigned_date_completed, due_date, notes, and the completed field (boolean). The name table is pretty simple and hold has the name and their unique id.

The first form (for giving assignments) has a list box that queries the table of names. (This used to be a drop down box where I could only select one name). Then, I have text boxes for everything else (and calendars for the date selections). The submission button makes a new table entry on the assignments table for the assignment that was generated.

I have a separate form that queries the assignments table and displays all of the assignments that haven't been completed yet. Once I check the completed box in this query, there is a macro "on_click" that refreshes the query and only displays the table entries that haven't been completed. This form also shows, how many haven't completed, due dates, etc. It's essentially my main screen to check the status of all assignments.

Again, there are other elements to the tracker, but they are not relevant to the current problem of; being able to create multiple table entries from the first form instead of it creating one entry and adding all of the names that were selected.

I hope this makes a little more sense now. It's easy to overlook the complexity when you are so focused on the project. Thanks again.

Will

If the assignments are fixed and repetitive, then you need a third table because otherwise you will NEVER be able to track activity. However, this third table can be pretty simple. You need to look up something called a JUNCTION TABLE on this forum using its search function (3rd from right in the thin blue ribbon). That is because otherwise you have no place to put the completion status - which is part of your complaint.

Okay, I will look up a JUNCTION TABLE, but tracking completed tasks is very easy now, I simply check the "completed" checkbox in the assignments table. I have a Boolean field with a check box for that specific purpose.

Unless I am overlooking something, I don't have problem tracking anything... I have a problem creating multiple table entries at once by filling out a form and clicking submit. basically;

- I want to select a group of names on my form (from a list box that queries the names table)
- fill in the rest of the text boxes and date boxes
- click submit and based on the names i select, it creates a separate entry for each name

I already have a functioning access database that does all of this minus the multiple names feature. I have to create an entry for each name individually and repeat as required for all names. It tracks everything perfectly, all of my queries and reports are good, I am simply wanting to change one thing about how entries are created.

I'll look into JUNCTION TABLES now and see if that will help me though. Thanks.

Spend 15-20 minutes reviewing this article.

Have a look at post 4 from a thread on this site here

I do something along the lines of what Tony was suggesting.

HTH

iamwill said:

Okay, I will look up a JUNCTION TABLE, but tracking completed tasks is very easy now, I simply check the "completed" checkbox in the assignments table. I have a Boolean field with a check box for that specific purpose.

Unless I am overlooking something, I don't have problem tracking anything... I have a problem creating multiple table entries at once by filling out a form and clicking submit. basically;

- I want to select a group of names on my form (from a list box that queries the names table)
- fill in the rest of the text boxes and date boxes
- click submit and based on the names i select, it creates a separate entry for each name

I already have a functioning access database that does all of this minus the multiple names feature. I have to create an entry for each name individually and repeat as required for all names. It tracks everything perfectly, all of my queries and reports are good, I am simply wanting to change one thing about how entries are created.

I'll look into JUNCTION TABLES now and see if that will help me though. Thanks.

Click to expand...

You have arrived at a juncture I believe we all get to.

You find this MS Access program and you start building a database, this is how most of us start I reckon. Everything works like a charm. You can create reports and queries presenting your information nicely. And then you want something a little extra, it seems such a simple step to take to get what you want. But it gets you scratching your head, how am I going to do this?

I know I'll ask on a forum. But the feedback you get from the forum isn't useful. They tell you that this database you have created through many hours of laborious work isn't constructed correctly. It's unbelievable, not what you want to hear, they must be missing something there must be something else I can do.

Occasionally Some people in this situation Get Lucky. A bored programmer (I've been there) decides to step in and play... They come up with some bespoke way to achieve the result you desire. You think they've done you a big favour, but really they haven't, they have just entrenched you further into something that will just become more and more problematic as you go on.

You are getting some good advice here. I know it's good advice, the people giving you the advice know it's good advice. The question you need to ask yourself is how do they know it's good advice? Because we've been down this road before! We have had the pain, the heartache and the disappointment.

However all is not lost you have obviously learnt some valuable techniques in your journey so far. This is not a setback, this is a brilliant learning opportunity.

Uncle Gizmo,

Thanks! I was actually laughing. And you are right, I am looking out my database structure now and trying to figure out how to re-design this so that it works with what I need. It's no secret that I am new to this and you guys have been doing it for some time, and you are surely right, I don't want change... but obviously, I either give up... or change.

So one thing that I have looked at is the JUNCTION TABLES. Although I see the purpose and usefulness of them, in my application, I don't think I can. My assignments will always change. I wish they were a static list like the names, but unfortunantely, they aren't. They are all a one and done task and wont be reused.

Now, with that said, maybe I just add the task/aqssignment name to a table, then use a junction table after that?

Like I said, I am trying to wrap my head around it.

As long as you have an assignments table with multiple people being assigned the same task (more or less), you need a third table to track the "multiple people, multiple assignments" environment. Otherwise you will run into a brick wall.

What you are not seeing is that there is a natural relationship among people and tasks that allows what we call the many/many relationship - and that can ONLY easily be handled by a junction table. If you can EVER have the case of two or more people assigned to the same task AND AT THE SAME TIME have two or more tasks associated with the same person, then you MUST have a junction table to make this work.

I *think* the issue here is duplication of data.
With a junction table you can only use the keys (ID of each table) to link records.

I'll give you a real world example.

I created a database to record my shipping company gazettes. These showed where crew were on ships on a certain date, and their rank at the time.

The DB has
Ships
Dates
Rank
Crew

To link all of these together I have a junction table that only holds the IDs of those tables. That table is the Links table, as that is all it holds are links

So in your DB, you would have employees and assignments.
With a junction table it would serve the same purpose as my links table. Multiple employees against multiple assignments. No other data is duplicated?

Later on when you need more info, you will come to realise why this has been suggested.

Been there and done that.

So, I just wanted to come back and close the loop. After restructuring everything and using junction tables, everything became VERY clear. Thanks. Although it wasn't the route I thought I needed, it was EXACTLY what I needed.

You guys were a great help and I really appreciate it.

Thanks for getting back Will. We're happy that your visit was successful and that your problem was resolved.
Good luck with future projects.