Table Arrangement: Timecard, Consumer Tracking, and Billing
I apologize, in advance, for the novel.
I'm making a fairly involved database and my knowledge of Access is barely scraping past mail merge. I'm improving and I think I have the general concepts (small number of forms display and feed data through a small number of queries (as needed) to tables whose data is used to generate reports), but a history of using a less-than-efficient Excel database makes minimizing my tables (to prevent duplicates) a challenge.
That said: I'm going to have a number of questions, and when I ask query/report/form questions, I will add a link to those threads (in their correct forums) as I ask them. I hope that's ok.
First) I'm referencing Microsoft's Time and Billing Template (h t t p://office.microsoft (dot) com/en-us/templates/time-and-billing-database-TC001018461[/url] (dot) aspx), and grasp most of what I see. However, what the heck is going on with the Time Card table in that database? I think it is used to create a bridge for the many-to-many relationship of the Time Card Hours and Employees tables. But I notice adding new employees (from the Employees form) does not add a new TimeCardID record (shouldn't it?) and I have no clue of the purpose of the "Date Entered" field if it doesn't correspond to when a new TimeCardID record is created.
Second) General premise, plan, and table layout.
I work in the Mental Health field and need to create a database that keeps the authorizations for the individuals we serve (consumers), their personal information, employee information, their hours worked, and generate billing & timesheet reports.
Each consumer has an authorization number (auth #), which is used to request additional services. Each auth # may have multiple requests, each request identified by a date range (auth instance). Each request is for 2 types of services (PSR & BST). Consumers may receive new authorization numbers, but old ones must be recorded and kept. Each consumer works with one or more employees, and each employee has multiple consumers. Also, each employee has 1 of many roles, each role associated with a service, and some allowed to do more than one service (each of which bill at different rates), and consumer-related expenses must be accounted for.
SO: 12 tables.
1) tbl|Auth Numbers - used for recording consumer's auth #s.
pk|AuthNumberID
nm|AuthNumber
fk|Consumer ID
2) tbl|Authorizations - used for recording each authorized date range (auth instance)
pk|AuthInstanceId
fk|AuthNumberID
dt|AuthStart
dt|AuthEnd
nm|PSRUnits
nm|BSTUnits
yn|Current - checkbox for identifying most recent/currently in use auth
3) tbl|Consumers - used for consumer and contact info
pk|ConsumerID
tx|ConsumerFirst
tx|ConsumerLast
(MANY other entries, only used in forms)
yn|Active - checkbox for identifying consumers currently working with (forms may display only current)
4) tbl|BusinessName
(All entries used for reports only)
5) tbl|Employees
pk|EmployeeID
tx|EmployeeFirst
tx|EmployeeLast
fk|RoleID
(MANY other entires, used only in forms)
$|HourlyRate -to expand for payroll services later
yn|Salary - to expand for payroll services later
$|SalaryPerPeriod - to expand for payroll services later
yn|Active - checkbox for identifying current employees (forms may display only current)
6) tbl|Expense Codes
pk|ExpenseCodeID
tx|ExpenseCode
7) tbl|Reports
pk|ReportID
(for use later with forms/report integration)
8) tbl|Roles
pk|RoleID
tx|Role
tx|RoleDescription
fk|ServiceID
9) tbl|Services
pk|ServiceID
tx|Service
$|BillingRate
10) tbl|Time Card Expenses
pk|TimeCardExpenseID
fk|TimeCardID
dt|ExpenseDate
tx|ExpenseDescription
$|ExpenseAmount
fk|ExpenseCodeID
11) tbl|Time Card Hours
pk|TimeCardDetailID
fk|TimeCardID
dt|DateWorked
fk|ConsumerID
nm|BillableUnits - time worked is measured in units, 1 unit = 15 minutes
12) tbl|Time Cards
pk|TimeCardID
fk|EmployeeID
...
WHEW. I apologize for the length, any formatting issues, and any stupidity. If there are problems with any of the above or they don't conform to forum rules, I can remove the post and do so however is more appropriate.
Thanks in advance!
I apologize, in advance, for the novel.
I'm making a fairly involved database and my knowledge of Access is barely scraping past mail merge. I'm improving and I think I have the general concepts (small number of forms display and feed data through a small number of queries (as needed) to tables whose data is used to generate reports), but a history of using a less-than-efficient Excel database makes minimizing my tables (to prevent duplicates) a challenge.
That said: I'm going to have a number of questions, and when I ask query/report/form questions, I will add a link to those threads (in their correct forums) as I ask them. I hope that's ok.
First) I'm referencing Microsoft's Time and Billing Template (h t t p://office.microsoft (dot) com/en-us/templates/time-and-billing-database-TC001018461[/url] (dot) aspx), and grasp most of what I see. However, what the heck is going on with the Time Card table in that database? I think it is used to create a bridge for the many-to-many relationship of the Time Card Hours and Employees tables. But I notice adding new employees (from the Employees form) does not add a new TimeCardID record (shouldn't it?) and I have no clue of the purpose of the "Date Entered" field if it doesn't correspond to when a new TimeCardID record is created.
Second) General premise, plan, and table layout.
I work in the Mental Health field and need to create a database that keeps the authorizations for the individuals we serve (consumers), their personal information, employee information, their hours worked, and generate billing & timesheet reports.
Each consumer has an authorization number (auth #), which is used to request additional services. Each auth # may have multiple requests, each request identified by a date range (auth instance). Each request is for 2 types of services (PSR & BST). Consumers may receive new authorization numbers, but old ones must be recorded and kept. Each consumer works with one or more employees, and each employee has multiple consumers. Also, each employee has 1 of many roles, each role associated with a service, and some allowed to do more than one service (each of which bill at different rates), and consumer-related expenses must be accounted for.
SO: 12 tables.
1) tbl|Auth Numbers - used for recording consumer's auth #s.
pk|AuthNumberID
nm|AuthNumber
fk|Consumer ID
2) tbl|Authorizations - used for recording each authorized date range (auth instance)
pk|AuthInstanceId
fk|AuthNumberID
dt|AuthStart
dt|AuthEnd
nm|PSRUnits
nm|BSTUnits
yn|Current - checkbox for identifying most recent/currently in use auth
3) tbl|Consumers - used for consumer and contact info
pk|ConsumerID
tx|ConsumerFirst
tx|ConsumerLast
(MANY other entries, only used in forms)
yn|Active - checkbox for identifying consumers currently working with (forms may display only current)
4) tbl|BusinessName
(All entries used for reports only)
5) tbl|Employees
pk|EmployeeID
tx|EmployeeFirst
tx|EmployeeLast
fk|RoleID
(MANY other entires, used only in forms)
$|HourlyRate -to expand for payroll services later
yn|Salary - to expand for payroll services later
$|SalaryPerPeriod - to expand for payroll services later
yn|Active - checkbox for identifying current employees (forms may display only current)
6) tbl|Expense Codes
pk|ExpenseCodeID
tx|ExpenseCode
7) tbl|Reports
pk|ReportID
(for use later with forms/report integration)
8) tbl|Roles
pk|RoleID
tx|Role
tx|RoleDescription
fk|ServiceID
9) tbl|Services
pk|ServiceID
tx|Service
$|BillingRate
10) tbl|Time Card Expenses
pk|TimeCardExpenseID
fk|TimeCardID
dt|ExpenseDate
tx|ExpenseDescription
$|ExpenseAmount
fk|ExpenseCodeID
11) tbl|Time Card Hours
pk|TimeCardDetailID
fk|TimeCardID
dt|DateWorked
fk|ConsumerID
nm|BillableUnits - time worked is measured in units, 1 unit = 15 minutes
12) tbl|Time Cards
pk|TimeCardID
fk|EmployeeID
...
WHEW. I apologize for the length, any formatting issues, and any stupidity. If there are problems with any of the above or they don't conform to forum rules, I can remove the post and do so however is more appropriate.
Thanks in advance!
Last edited:
