Mass Data Entry for a Junction Table

On my Junction table we track the employee and what education
offerings they have attended. We have found that we have multiple
employees attending the same offerings on the same day. (makes sense,
if a class is offered one day you will about 20-30 people in that
class)  Is there any way to simplify the data entry process.

Right now on the form the person doing data entry selects the
employee, the education offering and the date. This has to be done for
each attendee.

What would be great would be  to create a form that allows you to
select a course and a date, and then select multiple employees. (A
seperate record would need to be created for each employee on the
junction table.)

Does anyone know if this is possible?

0
Steve
1/27/2010 5:06:54 PM
access.reports 4434 articles. 0 followers. Follow

8 Replies
1138 Views

Similar Articles

[PageSpeed] 37

It should be possible using a form with a subform.

The main form would need controls to select a class and enter a date.
Link the Form to the subform on the date and course controls and the relevant 
fields in the junction table.  The subform would have a combobox to select the 
employee from in each record.

I am assuming that your junction table has employeeId, ClassID, and 
DateofClass fields.  Or if a class and date are stored in one record then you 
only need to bind to class, since the date is always the same for a class.

I might have a structure more like:

tblCourses (Things to be taught)
CourseID
CourseName
CourseProvider
....

tblClass (Details on a particular class session for a particular course)
ClassID
CourseID
ClassDate
....

tblEmployeeTraining (Junction table)
EmployeeID
ClassID

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Steve wrote:
> On my Junction table we track the employee and what education
> offerings they have attended. We have found that we have multiple
> employees attending the same offerings on the same day. (makes sense,
> if a class is offered one day you will about 20-30 people in that
> class)  Is there any way to simplify the data entry process.
> 
> Right now on the form the person doing data entry selects the
> employee, the education offering and the date. This has to be done for
> each attendee.
> 
> What would be great would be  to create a form that allows you to
> select a course and a date, and then select multiple employees. (A
> seperate record would need to be created for each employee on the
> junction table.)
> 
> Does anyone know if this is possible?
> 
0
John
1/27/2010 5:42:46 PM
I have done a form like this many times! You need a form/subform. You 
display the the class and date in the main form. In the subform you display 
a list of all employees and a checkbox next to each. ClassID and ClassDate 
are hidden fields in the subform. You need code in the Afterupdate event of 
the checkbox to assign ClassID and ClassDate:
Me!ClassID = Me.Parent!ClassID
Me!ClassDate = Me.Parent!ClassDate
When it's set up correctly, all you need to do is go down the list and 
check-off attendees. If the number of employees is small, you can display 
all the employees at once. If the number of employees is large, you can but 
buttons in the subform to display groups of employees such as:
A-F
G-L
M-R
S-Z

Steve
santus@penn.com

"Steve" <smiller@hospices.org> wrote in message 
news:c78a6256-0ad0-4462-95e2-56d189330a1e@k35g2000yqb.googlegroups.com...
> On my Junction table we track the employee and what education
> offerings they have attended. We have found that we have multiple
> employees attending the same offerings on the same day. (makes sense,
> if a class is offered one day you will about 20-30 people in that
> class)  Is there any way to simplify the data entry process.
>
> Right now on the form the person doing data entry selects the
> employee, the education offering and the date. This has to be done for
> each attendee.
>
> What would be great would be  to create a form that allows you to
> select a course and a date, and then select multiple employees. (A
> seperate record would need to be created for each employee on the
> junction table.)
>
> Does anyone know if this is possible?
> 


0
Steve
1/27/2010 7:31:51 PM
Tahnk you both Steve and John I will try these, I knew it had to be
possible!

Steve
0
Steve
1/28/2010 1:22:39 PM
On Jan 28, 8:22=A0am, Steve <smil...@hospices.org> wrote:
> Tahnk you both Steve and John I will try these, I knew it had to be
> possible!
>
> Steve

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs?
0
Steve
1/28/2010 2:19:58 PM
Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs?

On Jan 27, 2:31=A0pm, "Steve" <notmyem...@address.com> wrote:
> I have done a form like this many times! You need a form/subform. You
> display the the class and date in the main form. In the subform you displ=
ay
> a list of all employees and a checkbox next to each. ClassID and ClassDat=
e
> are hidden fields in the subform. You need code in the Afterupdate event =
of
> the checkbox to assign ClassID and ClassDate:
> Me!ClassID =3D Me.Parent!ClassID
> Me!ClassDate =3D Me.Parent!ClassDate
> When it's set up correctly, all you need to do is go down the list and
> check-off attendees. If the number of employees is small, you can display
> all the employees at once. If the number of employees is large, you can b=
ut
> buttons in the subform to display groups of employees such as:
> A-F
> G-L
> M-R
> S-Z
>
> Steve
> san...@penn.com
>
> "Steve" <smil...@hospices.org> wrote in message
>
> news:c78a6256-0ad0-4462-95e2-56d189330a1e@k35g2000yqb.googlegroups.com...
>
>
>
> > On my Junction table we track the employee and what education
> > offerings they have attended. We have found that we have multiple
> > employees attending the same offerings on the same day. (makes sense,
> > if a class is offered one day you will about 20-30 people in that
> > class) =A0Is there any way to simplify the data entry process.
>
> > Right now on the form the person doing data entry selects the
> > employee, the education offering and the date. This has to be done for
> > each attendee.
>
> > What would be great would be =A0to create a form that allows you to
> > select a course and a date, and then select multiple employees. (A
> > seperate record would need to be created for each employee on the
> > junction table.)
>
> > Does anyone know if this is possible?- Hide quoted text -
>
> - Show quoted text -

0
Steve
1/28/2010 2:21:16 PM
NO, you use the junction table as the source for the subform only.

You can have NO record source for the main table and just have a combobox that 
lists the courses (assuming you have a table of courses) and a textbox that 
has the course date.  You fill (select) values for the combobox and textbox 
and LINK the relevant fields in the junction table to the CONTROLS on the main 
form.

Alternative is that you have a table that has all the courses and course dates 
and you use that as the record source for the main form.  If you use the table 
(or better a query of the table) as a record source, then you can link the 
fields from the course list (master) to the appropriate fields in the 
sub-form's source.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Steve wrote:
> Again thank you both, I am struggling with tring to get the list set
> up in my subform. Do you use the Junction as the sourse for both the
> Form and the Sub Form? I would assume that I want to use the table
> that holds my Staff IDs?
0
John
1/28/2010 4:12:59 PM
If you see this - I will get back later with a detailed description on how 
to set it up. It may be tomorrow so keep checking back.

To start, you'll need the following tables:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblCourse
CourseID
CourseName
etc

TblClass
ClassID
ClassDate
CourseID
InstructorID (You need to set this up according to who teaches your classes. 
Could be ab employeeID)

TblClassAttendee
ClassAtendeeID
ClassID
EmployeeID

Steve

"Steve" <smiller@hospices.org> wrote in message 
news:e97ea8a5-70f1-41e7-a68f-89d533599cce@g29g2000yqe.googlegroups.com...
On Jan 28, 8:22 am, Steve <smil...@hospices.org> wrote:
> Tahnk you both Steve and John I will try these, I knew it had to be
> possible!
>
> Steve

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs? 


0
Steve
1/28/2010 7:07:01 PM
Add a field named Selected to TblClassAttendee.

1.    Create a query named QryFrmClass based on TblClass. Sort ascending on 
ClassDate.
2.    Create a form named FrmClass. Make QryFrmClass the recordsource of 
FrmClass. Nmae each textbox on the form the same as the field name in 
QryFrmClass.
3.    Create a query named QryClassAttendee based on TblClassAttendee. Set 
the creiteria for ClassID to Forms!FrmClass!ClassID
4.    Create a query that includes TblEmployee and QryClassAttendee. Join 
EmployeeID in TblEmployee to EmployeeID in QryClassAttendee. Click on the 
join line and make it a type 2 join. Name the query QrySFrmClassAttendee.
5.    Create a continuous form named SFrmClassAttendee with a recordsource 
of QrySFrmClassAttendee. Include the fields FirstName and LastName from 
TblEmployee, Selected, ClassID and EmployeeID from TblClassAttendance and 
EmployeeID from TblEmployee. Lock and disable the FirstName and LastName 
fields. Make ClassID and EmployeeID from TblClassAttendance and EmployeeID 
from TblEmployee not Visible. Name EmployeeID from TblEmployee 
"EmployeeIDfromTblEmployee".
6.    Put the following code in the AfterUpdate event of the Selected 
checkbox:

If Not IsNull(Me!Selected) Then
  Me!ClassID = Me.Parent!ClassID
Me!EmployeeID = Me!EmployeeIDfromTblEmployee
Else
  DoCmd.SetWarnings False
  DoCmd.RunCommand acCmdDeleteRecord
  DoCmd.SetWarnings True
  Me.Requery
End If

7.    Make SFrmClassAttendee a subform on FrmClass. MAKE THE LINKMASTER AND 
LINKCHILD PROPERTIES BLANK!!

When you open FrmClass, you will be able to navigate to any class you 
previously entered. In the subform you will see a list of all employees. 
Check the ones that attended the class. Each time you check, you will add an 
attendance record to TblClassAttendance. If you uncheck an employee, you 
will delete his record from TblClassAttendee that he attended the class.

Steve
santus@penn.com



"Steve" <smiller@hospices.org> wrote in message 
news:e97ea8a5-70f1-41e7-a68f-89d533599cce@g29g2000yqe.googlegroups.com...
On Jan 28, 8:22 am, Steve <smil...@hospices.org> wrote:
> Tahnk you both Steve and John I will try these, I knew it had to be
> possible!
>
> Steve

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs? 


0
Steve
1/28/2010 10:47:44 PM
Reply:

Similar Artilces:

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Integration Manager
I have set up an integration mapping into the Bank Transaction Entry for GP 8.0. Since there is no option to save a bank transaction entry, it posts automatically when I run the integration, but it does not print off the reports I usually get when I do a manual bank trans. entry (bank transaction posting journal & general posting journal). I was wondering if anybody knows how to get IM or GP to print the reports associated with the entry? Do I have to write a script? Thanks. I ran into the same issue. Boy I wish Bank Trx used batches that would solve this. We just did a repri...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

Pivot Table Summary Error?
Greetings, Sorry, wasn't sure which subforum to post this in, as there's nothing dedicated to pivot table. My dilemma is as follows: I have a spreadsheet where data is entered daily. For my example, just imagine a spreadsheet with three columns: date, date, delay. Two date columns are used for the pivot table. The pivot table is displayed with month and weekly ranges as the row headers. There is a field in the pivot table that is summarized as a MAX of one of the source data columns. Now, the problem I am coming across is the monthly summary MAX is not c...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

Mass Delete Contents of Inbox
How can I delete contents of inbox all at once. It would take me all week if I had to do it manually. It is mainly notifications on undeliverables to the system administrator. Edit | Select All, then press Delete. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Adin" <nnunnallee@hotmail.com> wrote in message news:%23hhkBfWfEHA.592@TK2MSFTNGP11.phx.gbl... > How can I delete contents of inbox all at once. It would take me all week &g...

One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A start_date and comp_date for start and completion date. I need to run a query that will return all the records between two dates selected by a user. Not hard, piece of cake. The problem I am having is that my query isn't returning all the records that overlap a given date range. For example: I select a date range: June 1 to September 1. My query will return 10 records that start and stop between June 1 and September 1. This is good and correct, but.... ...I have several records that start before June ...

Pivot table #10
I have saved the excel file with data in another name (as a temporary measure) and the pivot tables is now looking at the data in the 'new' file. Is there a way of persuading the pivot tables to look at the original excel file and data without having to use the pivot table wizard to re-make the pivot tables? Regards. Bill Ridgeway ...

how to use this to query my table?
I have a table with 30,000 records. I'd like to use this http://www.regular-expressions.info/email.html to query my email fields (show invalid emails). I'd like to use the first expression listed on that site. How do I proceed? On Sun, 16 May 2010 18:52:30 -0700 (PDT), Song <song.usa@gmail.com> wrote: Write a public function in a standard module: Public Function TestEmail(myEmailField as string) as Boolean Dim pattern As String pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b" Dim r As RegExp 'Requires a reference to Microsoft VBScr...

Pasting html table to Excel reformats certain values
Hi! When I paste an html table into Excel some cells are automatically formatted to a different datatype. My html table contains identifiers such as "6632/04" which I want to store as text. Excel assumes that data like this is some sort of date and displays "apr-32" instead when I paste the table. If I try to apply the text format to the column, Excel converts the values to some type of integer "1728420" which looks like it could be the integer value of the date. It isn't possible to get the original value back! How do I turn off Excels automatic formatting...

Missing Data Options
I am trying to use Tools - Options - Chart. I want to use the "not Plotted" option. When I click on it my chart does not change. This happens after I've selected the chart and then trying to make these changes. What am I doing wrong? I want to create a chart that ignores missing data, and gives me a trend line based on the data I have. I've had my nose in my Excel "bible" but can't find the answer. I greatly appreciate any tips. Jim -- jimsmith888 ------------------------------------------------------------------------ jimsmith888's Profile: http...

Extracting data and returing to colums.
I am about half way there, but I need some more help. These are web inquires that parsed out in the same colum and repeat th same way all down the page. I want to be able to use the fields as guidelines to pull the dat adjacent to it and sent to the new colum. In a way it is transposin all the data, but I need to pull all the first names of these peopl and have them align in the first colum. Same for Last name, business etc. So if the information that I have come across like this in th spreadsheet, I just want the customer infomation to move to th appropriate colums like the third example...

how do I convert Mozilla thunderbird data to outlook 2010?
Converted from an old Outlook to Mozilla Thunderbird years ago. Like new Outlook and would like to switch back. Can't seem to see a conversion utility -- Mishka I suspect you export from thunderbird to a format Outlook can import, eg csv (assuming pop mail) "Mishka" <Mishka@discussions.microsoft.com> wrote in message news:509C3250-167B-4FFF-9E07-5E5A8C625AA8@microsoft.com... > Converted from an old Outlook to Mozilla Thunderbird years ago. Like new > Outlook and would like to switch back. Can't seem to see a conversion > utility > -- >...

Graphing daily data
I am trying to graph daily data for a month in Excel 2007 that groups like days (Saturdays, Sundays, etc) in the same series, while still showing individual values and dates. Any ideas on how to accomplish this? Use your worksheet to pull the relevant data together into a series. Do the data manipulation in your worksheet, then the chart plots the series which you have generated in the worksheet. -- David Biddulph "Fozzie" <Fozzie@discussions.microsoft.com> wrote in message news:EB004134-AD88-434C-9DAD-BFE4CB2FD058@microsoft.com... >I am trying to graph daily data f...