Hello all!
I have been beating my head upon many walls the last week or so trying to figure out what to do for the database design for my small business(a ranch). I have been perusing this forum and many others for the answer(s), but can't quite wrap my head around it!
In my head, it's simple - I need to manage employee, customer and horse information as well as create invoices for services provided. Here's what I'm pretty sure I have done RIGHT so far:
tblEmployees
pkEmployeeID
FirstName
LastName
etc...
tblCustomers
pkCustomerID
FirstName
LastName
etc...
tblHorses
pkHorseID
Name
etc...
NOW here's where things get complicated for me. I have a multitude of services with different costs:
tblServices
pkServiceID
CostPerRiderPerHour
...and then there is the specific record of the customer's current visit:
tblInvoices
pkInvoiceNumber
fkCustomer
Date
NoOfRiders
Time
BUT what's missing from tblInvoices is Employee, Horse and Service because any customer's visit could have more than one of each! I tried adding three junction tables:
tblEmployeeInvoice
fkEmployeeID
fkInvoiceNumber
tblHorseInvoice
fkHorseID
fkInvoiceNumber
tblServiceInvoice
fkServiceID
fkInvoiceNumber
...but it doen't seem to relate properly! One of the end goals would be a form where I or an employee could check off all employees, horses and services (as well as the date, number of riders and the amount of time riding) needed for a costomer's visit. Every time I try to create a query and base a form on it, Access doesn't display the form fields.
I hope I've been clear on this! If need be, I can attach the mdb (Access 2003) after some info clearing!
Thanks for your time!