Subform/Subreport

[vcarrill]

Hello!

Can you please advise on how I could have the following show up by default in the subform for every single person?
I have (1) table with:
Firstname
LastName
EmpID

The second table with:
EmpID
AntennaType
AssemblyNumber
TaskName

Here is an example of the data:

Thank you for the help!

 

[theDBguy]

As long as we're not talking about placeholder records, you can use an APPEND query to assign the child records in one go.

 

[vcarrill]

So would this permit the information to be available in the subform? I have several to add and need their leads to be able and update their status if they are In Training, Proficient, for every Task. Thank you

 

[theDBguy]

So would this permit the information to be available in the subform? I have several to add and need their leads to be able and update their status if they are In Training, Proficient, for every Task. Thank you

Whatever you add to the table, via a query or manually, should be available in the subform.

 

[Pat Hartman]

Can you please advise on how I could have the following show up by default in the subform for every single person?

I don't see any relationship between the two tables. How would you decide which person was associated with which antenna?

You might want to post a picture of what you are trying to achieve.

 

[vcarrill]

I don't see any relationship between the two tables. How would you decide which person was associated with which antenna?

You might want to post a picture of what you are trying to achieve.

My apologies I failed to add EmpID to the 2nd table, please see my correction above, thank you.

 

[Uncle Gizmo]

I have a Blog where I demonstrate how to automatically add a Checklist in the subform on a main form. Often people have a sub-form which needs to be filled with the same data for every customer and/or order. The data operator has to enter the individual items in the sub form. In my blog Here:-

Add a Check List to your MS Access Database - Nifty Access

Add a Checklist to MS Access Checklists are very useful. However, they can be a pain both for developer and for the user. The last thing you want is a subform comprising of a set of of a combo boxes that the user has to select in order to provide the question and the answer!

www.niftyaccess.com

I take you through the process of creating the checklist and how to automatically add it. I think you need to follow a very similar process to solve your problem.

 

[Pat Hartman]

If I understand the question correctly, you would create a main form for the employee and a subform for the other table. A subform is simply a form that is shown in a subform control on a main form.

If you have create the table relationships using the relationship window, Access will automagically connect the main form with the subform when you add the subform. If the two tables are not related, you will manually need to choose the master/child relationship so Access will know how the two tables are connected.

 

[vcarrill]

If I understand the question correctly, you would create a main form for the employee and a subform for the other table. A subform is simply a form that is shown in a subform control on a main form.

If you have create the table relationships using the relationship window, Access will automagically connect the main form with the subform when you add the subform. If the two tables are not related, you will manually need to choose the master/child relationship so Access will know how the two tables are connected.

Ms. Hartman, I did as you stated above. The issue I am having is that I need all of the "data" to load in the subform for every employee as a default. I am unsure on how to accomplish this?

Basically there is a core of processes that every employee is taught by product. The leads use this as a training guide and after every single process they indicate where the employee stands in their training (In Training, Proficient, etc).

I hope this provides further clarity on what it is I am trying to do with Access versus using the current Excel sheet that is quite cumbersome.

 

[Pat Hartman]

Sounds like what you are missing is a third table. This table is sometimes called a junction table because it joins two entities and I think that is what you might be trying to do. It is how you implement a many-many relationship

In this case, the subform would be bound to the junction table.

You have two views of data. One from the perspective of the employee and the other from the perspective of the product. The junction table would contain the EmpID and the ProductID (or whatever the PK of the other table is called). That implements the m-m by connecting the two tables. You also have what we call intersection data. It is data that is related to BOTH of the joined tables. For example, you might have the date a course was completed and a grade.

I'm guessing though that the application needs more than what I have described. You might also need certifications and another junction table for when they were earned.

For now, I'm uploading a m-m example. See if you can, in your mind, translate it to your situation.

 

[vcarrill]

Sounds like what you are missing is a third table. This table is sometimes called a junction table because it joins two entities and I think that is what you might be trying to do. It is how you implement a many-many relationship

In this case, the subform would be bound to the junction table.

You have two views of data. One from the perspective of the employee and the other from the perspective of the product. The junction table would contain the EmpID and the ProductID (or whatever the PK of the other table is called). That implements the m-m by connecting the two tables. You also have what we call intersection data. It is data that is related to BOTH of the joined tables. For example, you might have the date a course was completed and a grade.

I'm guessing though that the application needs more than what I have described. You might also need certifications and another junction table for when they were earned.

For now, I'm uploading a m-m example. See if you can, in your mind, translate it to your situation.

This is amazing, thank you! The last bit of assistance I need is the input mask for the following: 123456-500 and 123456-500ABC. I cannot seem to get the field to accept these type of entries. Thank you very much Ms. Hartman.

 

[Pat Hartman]

You're welcome. Help didn't help you with the input mask? I can't do the research right now. Maybe someone else can help.