The mention of those two items gives me the impression that you are trying to duplicate spreadsheet functionality in MS Access.
I'm sure it will be possible to achieve what you want but if you approach it with a spreadsheet design in mind then it's going to be at best problematic and very possibly a complete failure.
So I strongly suggest you start at the beginning with a story of what you are creating and what you want to end up with...
This will give everyone the opportunity to make suggestions that should get you to a usable and suitable solution.
Yea you're definitely right, I'm using it similar to a spreadsheet. Maybe you (or someone else) can give me some advice on a redesign to help streamline this process.
Basically its a table that has appointment types as field names. Roughly 25 of them.
Then in the first column are employee names.
And then the cells in columns 2-25 are all the employees preferences for each appointment
Further complicating the matter is I have roughly 20 of these tables; they each represent a different location. The field names in each are exactly the same. I thought about merging them but didn't because some employees work at multiple locations and have different preferences based on that location.
As you can probably tell, this was a direct import from Excel.
TblEmployees
EmployeeID ' a primary key can be an autonumber
EmployeeFirstName
EmployeeLastName
. any other fields identifying an employee
TblLocations
LocationID ' primary key can be autonumber
LocationName
...Other location fields
TblPreferences
PrefernceID ' primary key. Not sure what a preference is there may be code you use
PreferenceDescription
TblAppointmentType
AppointmentTypeID ' primary key
AppointmentTypeDescription
.. other appointment type fields
now a junction table
TblEmployeeAppointmentPreferences ' might want something shorter
EmployeeID_FK ' a key linking to employee table
LocationID_Fk 'links back to a location
PreferenceID_FK ' links back to a preference
In this table
1 1 2
1 1 3
1 1 7
2 1 2
2 2 4
2 2 11
So this for example shows that employee 1 at location 1 has preferences 2, 3, 7 and employee 2 at location 1 has preference 2 and at location 2 has preference 4, 11.
With a query you can pull all the details together to show the names and descriptions of what all of this means.
EmployeeID_FK ' a key linking to employee table
LocationID_Fk 'links back to a location
PreferenceID_FK ' links back to a preference
In this table you would store values like (however the UI would show names and descriptions not the keys)
Code:
1 1 2
1 1 3
1 1 7
2 1 2
2 2 4
2 2 11
So this for example shows that employee 1 at location 1 has preferences 2, 3, 7 and employee 2 at location 1 has preference 2 and at location 2 has preference 4, 11.
With a query you can pull all the details together to show the names and descriptions of what all of this means.
My user interface for adding and editing would likely be a Main form with two subforms. In the main form you can navigate to an employee. In the first subform you can add or navigate to a location. In the second subform you can see the preferences for that employee at that location. You can add or delete preferences in the second subform.
I forgot appointment type. So the junction table would be
tblEmp_Loc_Appt_Pref
Code:
EmployeeID_FK ' a key linking to employee table
LocationID_Fk 'links back to a location
ApptTypeID_FK 'links back to appointment type
PreferenceID_FK ' links back to a preference
If I got it right then you store a record for An employees preferences by appointment type for a location.
A form to filter and find, may be different than the form for entering information my be something like
Employee as the main form
subform Location
Subform appointment type
and subform to choose preferences.
That is a kind of confusing form since it is a four table junction. making something that is intuitive would take some work, but is doable.
My navigation form would be simpler. It would be a tabular form showing every record.
Employee name, Location Name, Appointment type, Preference
The detail information comes from joining the junction table to all the related tables.
At the top of the form I would have unbound comboboxes. You can select 1 or more combos.
You select John Smith and see all records for all locations and appt types.
You Select John Smith and Location A you see all records for John at Location A
You selet John, A, and Appt Type X you see ....
Then From the navigation form you can click on a found record an it could take you to a detailed record if needed.
Doing this is a report is nice can you can then use sorting and grouping. you could group by Emp, then location, then appointment type
or make another report where you first group on location, then employee, then appt type. Same information just a different view.
I recommend you make your tables first and post back and upload the db. If you have some dummy data you can put it in the tables. Then we can help with the forms.
FYI, building a user interface to work with junction tables (add, edit, delete data) is a little tricky if you have never done it before. Not hard, but usually need to see it once. That is also why I like to separate data entry and data navigation. Doing that in one form will be complex and not intuitive.
The common database is 1 to many (parent to child). An employee can be in charge of many projects so you assign projects to an employee through a related key (Assuming only one person in charge). But you can have many people working on the same project and each person could work on many projects. This is a many to many, like you have and requires a junction table.
A common problem facing Excel developers moving into MS Access is the problem of converting the data from the spreadsheet format into a format suitable for Microsoft Access. I cover it in my blog here:-
I also provide a free tool for normalising the data (the usual term used to describe this transformation) The tool can take your field names, the row names, and the data and put it into three columns in a new table.
There should be a link to the tool in the "Excel in Access" Blog, along with instructions on how to download it for free, basically subscribe to my newsletter!
You might find it easier to transform your data in Excel. Mind you if you have a number of sheets, it might be easier to import all those into one table and then do the transformation.
Lets say I made tables for employees, locations, and appointment types.
And then I link those up via a junction table, and then make a form to begin data entry.
Ideally, I'd like to have three drop down selections, one for each category (employee, location, and appt type) and then a text box to type in the "preference" once those three are selected. Save and then that entry is complete.
Is that possible? The preferences are so personalized. I'd much rather type them into a form rather than pre-make a table with all of them. There's so many different preferences.
Ideally, I'd like to have three drop down selections, one for each category (employee, location, and appt type) and then a text box to type in the "preference" once those three are selected. Save and then that entry is complete.
Is that possible? The preferences are so personalized. I'd much rather type them into a form rather than pre-make a table with all of them. There's so many different preferences
Yes very easily. That would actually be the easiest form you could make and would not require any forms or subforms.
The first three emp, loc, and appt type would be combo boxes the last would be a textbox. You could sort the form by emp, loc, appt type.
One trick with combos is that you show the description, but store the id. So you would see john smith in the pull down but in the table you actually store the employeeID.
Perfect! Its building very necessary. Just gotta do data entry now.
One more question.
What if we receive new employees? What would be the method for adding them into the database?
I guess I could have a form separate form that adds new employees, and then you go to the junction form to setup their preferences, but that seems at bit redundant.
I guess I could have a form separate form that adds new employees, and then you go to the junction form to setup their preferences, but that seems at bit redundant
As mentioned you may want to have a master form and then at least one subform. The master form would be employees with details on the employees. The subform would be in continuous view combos for location, appt type. You then can add a new employee on the master form.
So your form might look like
Code:
Main Form
Name: John Smith SomeOtherField:
Phone: 123-456-7890
Position: Lead Engineer
Sub Form Preferences:
LocationA ApptType1 PrefABC
LocationA ApptType1 PrefCDE
LocationB ApptType4 PrefABC
LocationB ApptType5 PrefXXZ
However if you have a multiple preferences per appt type, and location. Then you might want a second subform for location. I prefer synched subforms instead of nested.
Code:
Main Form
Name: John Smith SomeOtherField:
Phone: 123-456-7890
Position: Lead Engineer
Location Sub Form
Location A
Location B
Sub Form Preferences:
'if you click on Location A in the location subform you see and can add a record for location a
ApptType1 PrefABC
ApptType1 PrefCDE
'if you click on Location B in the location subform you see and can add a record for location b
ApptType4 PrefABC
ApptType5 PrefXXZ
Omg that would be so perfect. The only thing I'd change is switch the locations to the main and form. Because there's roughly 17 locations but 120 employees. So it'd be much easier to choose the location and then see the employees that fall under that.
I attached a dummy database with the three tables (employees, locations, appt types) and the junction table. The junction table has the correct employee and location correlations, but not appt types yet. I'd like to craft a form first and then add in the appt type entries because there's so many of them (each employee has 30 different appt types)
Is there any way you can add that second coded form in the dummy database. I literally have no idea how to build that but it would make data entry for the appt types and preferences so easy.
The junction table has the correct employee and location correlations, but not appt types yet. I'd like to craft a form first and then add in the appt type entries because there's so many of them (each employee has 30 different appt types)
If every employee will have a record for every location and every appt type. You can preload the entire database and then just enter the preferences. If you have a table of employees, locations, and appointment types you make a Cartesian query. This would give you a 170 X 20 X 30 table of all combinations. You can then do an insert query into your junction table with everything but the preferences.
No its actually the opposite; pretty much all the employees work from a single location. There's only a handful (maybe 10) that cross over into another location
I listed these out in the junction table. You'll see there's a few that work at 2-3 locations, but for the most part each employee works from a single location
Couple of things. Do not use spaces in any field or table names.
Appt_types, ApptTypes for example.
Most people identify with a three letter prefix
tblApptTypes 'table
frmAssignPreferences 'form
rptPreferencs 'report
txtBoxPreference 'text box
etc
Do not name all your ID fields ID. It can get confusing when you join use something like.
EmployeeID (or as I explain later EmployeeID_PK)
LocationID
I would be more descriptive in field names. Also do not use names that could be VBA or SQL names. So instead of "Name" you would want "EmployeeName". Similarly you would not want fields with names like "Date, Year, Table, Field, Value, Text, Select, Where " etc. Normally you can avoid this by being a little more descriptive.
Instead of table "Junction" you likely want something more like "tblJuncEmpLocApptPref" so you can look at that and know what it is junctioning.
Field names do not need to be shown you can use captions and labels. So EmpName can be displayed as Employee Name.
Finally in your junction table you need a field for the ApptType and Preference.
A minor thing, but makes it easy for me to quickly see how tables relate.
In my tables for the primary key I use a PK suffix and in the related table I use an FK suffix meaning foreign key.
So in my employee table my autonumber would be
EmployeeID_PK
In my junction table it would be EmployeeID_FK, and LocationID_FK, ApptTypeID_FK.
I listed these out in the junction table. You'll see there's a few that work at 2-3 locations, but for the most part each employee works from a single location
If they get all the appointment types then you can still do this.
so you can take that data and cartesian it with the appt types. Then you will get the 30 appt types for each employee at each of their locations. Is it one preference per appt type per location per person, on can there be multiple preferences. The table supports both, but how you display this may change if more than one.
