Search results

You are absolutely correct. Today's "unique" key is tomorrow's problem. I worked on a £40 Million + project once and when I was asked to comment on the database found that the primary key of the customer table was FirstName + LastName + DateOfBirth... really, this is not a joke! When I...

Because, in this scenario, A case would be related to an Expense which, in turn, (if it is a time-related expense) would be related to Time which would be related to the Employee booking the time. I'd go back to first principles and confirm the relationships by writing them down as in: One...

Jeff. I'd be delighted and honoured. My hope is that I'll get thousands of beginner programmers started with the free mini-course there and that they'll adopt at least some of my design guidelines thus preventing much swearing and gnashing of teeth in their future careers!

Hi JWO Here's what I meant (see attachment). The primary key in the join table is compound, made up of three foreign keys (data type number). Great job with your table/field naming by the way. It is great to see my design rules being used! Only thing I'd change is Contacts and Calls in...

Hi I couldn't find a suitable book to use as courseware for my "Smart Method" Access VBA courses so I wrote and published my own. Modesty does not allow me to recommend it (as I am, of course biased) but thousands have downloaded a free beginners course from my site which consists of the four...

Hi Ray When I see a relationship going around in a cirle in a schema (relatepic2) it always rings alarm bells. Sometimes it is OK but it is more often a sign that there is a modelling error. I couldn't see anything wrong with your schema until I began to think that Time is an expense as well...

Hi Motbar You've missed the many-to-many relationship. When you're starting with database design simple models only need one-to-many relationships but you can't model your problem without a many-to-many (and that means a link table). The group is also a wildcard as it muddies the water...

Hi JWO You need two more tables. Association and ContactsAssociation. The Association table contains AssociationId and AssociationDescription (as a minimum). Examples would be Historic Customer|Existing Customer|... and any other inter-company relationship you need in future. The...

Yes. Payroll_number is a foreign key in the Holiday table so you can have lots of different records in the holiday table, even if they are all on the same day, linked to as many employees as you want. I won't consider business rules yet but you'd probably want to do some validation as holidays...

Hi Mia It is quite possible to do this elegantly with VBA code. Generate an SQL query, submit it to the Access database, walk the recordset and populate an Excel Range object with the result (via the Cells collection). Another approach might be to create the SQL query from within Excel...

Mosh In that case Payroll_number becomes a foreign ksy in the Holiday table and Holiday_booking goes in the dustbin.

Mike... not Mark! That's not what I meant. Do you want to create a record called (for example) "Independence Day" and link it to several employees or will every record be linked to one, and only one, employee. In this case you can still have six records, each linked to different employees...

Hi Rob My view is that if you don't normalise your database you'll end up doing lots of clumsy work-arounds that simply won't be efficient and you'll probably end up with a corrupt database. It's never worth it! There's one technique I use that massively speeds up database clients. I use it...

Hi Mosh I see you've re-designed! Did you read the design guidelines on my site? There's still some basics that could be improved. > I have removed the "Leave" table and inserted the field "leave_type" in table "holiday". Perfect > As for the allocated_holiday table, I thought I could...

Hi Rainman When designing a database it is good to "talk through" the relationships. One Employee is related to many Cases but a Case is only ever related to one Employee One Employee is related to many Tasks but a Task is only ever related to one Employee One Employee is related to many...

Hi Bob I'm interested in this observation. If the main table has no default value for the foreign key (ie it will default to null) wouldn't that cure the issue you refer to? Assuming an autonumber primary key in the related table Access always defaults the foreign key to 0 strangely and...

Hi Gomer Lots of information in your post. I've helped literally hundreds of businesses to make the big jump from Excel spreadsheet to Access so you are in a very big club. Sometimes it is hard to make the jump from flat file mindset to relational mindset but once you've done it you'll wonder...

Hi Mosh Great post. Very good idea to post your schema here too, if you get the schema dead right the reports/forms etc take care of themselves! Your schema looks good. Here's my first thoughts:- Employee/Manager realtionship, perfect Employee/Shift relationship perfect too...

Greetings We've been discussing this subject in the VBA coding forum and I've moved it here as it seems more appropriate. It was sparked by the publication of a set of design standards for Access applications on my web site www.learnaccessvba.com in which I advocate meaningless primary keys...

Dear Doc Man I think that I'm addressing case 4 as my students need rules to give them a quality standard that will get them started. If you do read my rules, however, you'll see that the introduction broadly agrees with the above. I would add, however, that just as all useful applications...