Looks like this has been continued:
https://www.accessforums.net/showthread.php?t=74156
so please add any followup there.
Looks like this has been continued:
https://www.accessforums.net/showthread.php?t=74156
so please add any followup there.
Missed that...
Deleted this post and move to other thread. Thanks Paul.
No worries. Looks like it hid on a new page.
Sorry for any confusion. I thought since the topic had changed from design to forms I should move the question. I forgot to delete my last entry on this thread.
If you use lookup tables in a split database does that mean everything will be slowed down as every time your form tries to access that table it will have to look into the backend?
You would need an atomic clock to notice the difference unless (maybe) you had issues with network speed. If that were the case, you would already be complaining to someone. The bigger payoff comes from being able to control what users can choose (and even enter if designed properly), plus not having to redesign a form to add an option to a control's list should you otherwise take that approach. In fact, it should be far faster than searching through thousands/millions of rows with a DISTINCT predicate on a combo's row source.
Admittedly I've not been dissecting all the responses but could share an observation or two. Would you ever need additional "interruption" options besides lunch or break1/2? If so, you definitely have those in the wrong table. What if you needed to add 'meeting attendance' or 'medical visit' or something else? If you think not, then you may be ok with what you have.
The other observations would be
a) be consistent with your spelling. You may find that it causes issues when designing/troubleshooting when you forget that exception and associate is spelled differently in just one place.
b) you would find it easier to design/troubleshoot if you could more easily identify controls from one another, or tables, or queries, etc. I suggest you adopt a more standard naming convention. You can raise errors when Access has to decide what you mean by TaskCode although the issue is not real common. I say this because you don't use any convention with your table names, so I wouldn't expect you'd do so with controls. If I use the form wizard to make a quick form, I go through all of them and rename them according to my convention. Here's some info on naming things (sorry if I've already posted these to you) :
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
You asked if this is ready, and I give you the following points/questions:
1) In tbl TaskExcpitions you are stating that for a AssociateID will have an ExceptionID, ExceptionCode and a ExceptionDuration. So that means for each different Duration, a new record will be required in that table. Are the ExceptionDuration a standard length, or AssociateID always does it the same length?
2) In tbl WorkDays, the value of WorkDate will be repeated for each AssociateID that worked that date. Do you see any additional Information require around the WorkDate (weather, staff level (sickness), unusual work occuring (door repair, stock count) that would effect everyone?
3) In tbl AssociateList will require a new record each different shift that person works (and for each Department/Subdepartment).
4) Something feels wrong with TaskActivities around TaskListID and TaskCode.
A good method of checking is to create a Excel workbook with a sheet for each table. Enter some data (1-3 dozen records) trying for different types of things, and then try joining them using Vlookup (or Index/Match). If you see a lot of repeating data you may not be normalized, if you can't get the joins to return the correct data, then the structure may not be correct. Don't worry about grammer or format, just does it work.
Please note that I use MAY. Like all things there is a BEST, Better, Workable, and Poor ways of doing it. You are the boss, you know the data, the way this will be used, the growth potential.
Let me take each one of these at a time.
Remember I am entering daily task activities for each associate. I am using the AssociateID number to tie each day together since a new one is generated every time I enter in their data. During the day each associate can have multiple exceptions during each task they perform and each one at varying lengths of time. This way I can enter in as many exceptions as I need for the associate for that day and the AssociateID ensures that it is all tied to the same day.1) In tbl TaskExcpitions you are stating that for a AssociateID will have an ExceptionID, ExceptionCode and a ExceptionDuration. So that means for each different Duration, a new record will be required in that table. Are the ExceptionDuration a standard length, or AssociateID always does it the same length?
On the back of the associate daily tracker that they fill out is a table that list all the agailable exceptions they can use during the day and the codes to show which one they are claiming or can claim during the work date.2) In tbl WorkDays, the value of WorkDate will be repeated for each AssociateID that worked that date. Do you see any additional Information require around the WorkDate (weather, staff level (sickness), unusual work occuring (door repair, stock count) that would effect everyone?
This is by design as I will be entering daily activity for each associate. In the end I will build my reports to show performance % from the individual task level. to daily, to weekly, to overall monthly average for each associate.3) In tbl AssociateList will require a new record each different shift that person works (and for each Department/Subdepartment).
Need to be more specific.4) Something feels wrong with TaskActivities around TaskListID and TaskCode.
And lastly, I am recreating this from a very extensive excel workbook that I have been building and perfecting for the last year. What started out as a simple spreadsheet now has mind of it's own almost. Its is close to a smart spreadsheet. There are auto generated individual associate worksheets, reports, and data archives. The spreadsheet can auto populate itself when adding new associates as well as remove any trace of them when they leave the company. And tons more. I am fairly proud of it. I know how the data comes in and how the data flows throughout the system.
But my greatest accomplishment is also my greatest hindrance. Excel is not very stable and needs to be fixed a lot. I am looking for a better way to work with the data and access is it. I am fighting it though, every step of the way, because I am trying to build a spreadsheet in a database and it's not working. I am hoping you guys here can help beat the excel out of me and show the the way in access.
I suspect you are at the point where you should backup the db as is, then try entering & modifying data and see what you get when you use it in every possible scenario that you can think of. The level of uncertainty on our part as to how things will really work probably isn't helping you a whole lot at this point.
Great! Its all done. sarcasm off
I don’t need to know why you are right or correct, but I pointed out areas that I thought you needed put some more thought in. A single table can be made to work.
I wasn’t saying to convert to excel, but to use it to check both normalization and structure. (What Mircon is saying to)
Looking at it now I see your field names tripped me up on the first one. The fourth one has the same two fields in both tables.
(See Mircon’s post on conventions)
Second and third ones need you think, just not what you have now, but outside the box too. If you are okay with what you have then GREAT.
There is no right or wrong in access (just like excel), just many ways of doing it, some pretty and some pretty ugly.