RobertLanders
Programmer
This is a bit of a long-winded question, so please bear with me.
I'm trying to automatically generate an invoice based on a rudimetary timesheet database I have set up in MS Access 2003
So far, I have been able successfully to generate a simple invoice that lists out each timesheet entry for a given file, and to calculate the amount of time spent on the task and its value after applying the applicable hourly rate for the person who performed the task in question. Each row in the list looks something like this:
Date ---- Work Done ---- Employee ---- Total Time ---- Fee
At the end of the report, I am able to generate a total fee for all of the timesheet entries in question. So far so good.
However, I now need to generate a more complicated form of invoice, and this is where my problems rest.
The first page of my invoice needs to give a summary of the relevant charges - not only for professional costs (charged on a time basis) but also for all outlays/disbursements spent by the firm on a particular file. The second (and following pages) then need to list all the timesheet entries in the same manner set out above.
The first page needs to look something like this:
Professional costs ----- $xxx
Outlays:
Disbursement 1 --- $xxx
Disbursement 2 --- $xxx
Disbursement 3 --- $xxx $xxx
Total: $xxx
I'm having difficulty in two respects.
At the moment, my simple database is based on a query that uses the FileNumber as the main link (primary/foreign key) for the tables in question. What this does is essentially create a separate row (with all the file details included) for each timesheet entry.
For example, assume that I have one table called tblFiles with the fields [FileNumber] and [ClientName] and a second table called tblTimesheet with the fields [Date], [FileNumber], [WorkDetail], [Start], [Finish], [Employee], [EmployeeRate]. Assume also that I have a query which joins these two tables based on [FileNumber] being equal.
If I have three timesheet entries in tblTimesheets, currently I end up with three rows, that look like this:
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate]
Now assume that I have a third table which stores all my outlays/disbursements information (tblOutlays). I simplified terms, this table has the following fields: [FileNumber], [Date], [Disbursement_Description], [Amount].
My first problem arises when I try to create a join between my existing query and tblOutlays. This is because the newly combined query results in the rows from tblTimesheets being repeated - once for each entry tblOutlays. For example, if I have two disbursements stored in tblOutlays, my query results in the following output:
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate],[Disbursement1]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate],[Disbursement1]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate],[Disbursement1]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate],[Disbursement2]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate],[Disbursement2]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate],[Disbursement2]
As a result, my list of timesheet entries ends up looking like this:
[Date], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate]
[Date], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate]
[Date], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate]
[Date], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate]
[Date], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate]
[Date], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate]
In addition, my calculations ends up doubling the value of the work performed.
So my first question is, how do I stop my timesheet rows being repeated in my invoice/report (and their values being summed more than once)?
My second problem/question, is how do I get the disbursements (ie the entries in tblOutlays) to list out on the first page (once only) whilst forcing the timesheet entries (ie the entries in tblTimesheets) to list out (again, once only) on the second and later pages?
TIA
Bob
I'm trying to automatically generate an invoice based on a rudimetary timesheet database I have set up in MS Access 2003
So far, I have been able successfully to generate a simple invoice that lists out each timesheet entry for a given file, and to calculate the amount of time spent on the task and its value after applying the applicable hourly rate for the person who performed the task in question. Each row in the list looks something like this:
Date ---- Work Done ---- Employee ---- Total Time ---- Fee
At the end of the report, I am able to generate a total fee for all of the timesheet entries in question. So far so good.
However, I now need to generate a more complicated form of invoice, and this is where my problems rest.
The first page of my invoice needs to give a summary of the relevant charges - not only for professional costs (charged on a time basis) but also for all outlays/disbursements spent by the firm on a particular file. The second (and following pages) then need to list all the timesheet entries in the same manner set out above.
The first page needs to look something like this:
Professional costs ----- $xxx
Outlays:
Disbursement 1 --- $xxx
Disbursement 2 --- $xxx
Disbursement 3 --- $xxx $xxx
Total: $xxx
I'm having difficulty in two respects.
At the moment, my simple database is based on a query that uses the FileNumber as the main link (primary/foreign key) for the tables in question. What this does is essentially create a separate row (with all the file details included) for each timesheet entry.
For example, assume that I have one table called tblFiles with the fields [FileNumber] and [ClientName] and a second table called tblTimesheet with the fields [Date], [FileNumber], [WorkDetail], [Start], [Finish], [Employee], [EmployeeRate]. Assume also that I have a query which joins these two tables based on [FileNumber] being equal.
If I have three timesheet entries in tblTimesheets, currently I end up with three rows, that look like this:
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate]
Now assume that I have a third table which stores all my outlays/disbursements information (tblOutlays). I simplified terms, this table has the following fields: [FileNumber], [Date], [Disbursement_Description], [Amount].
My first problem arises when I try to create a join between my existing query and tblOutlays. This is because the newly combined query results in the rows from tblTimesheets being repeated - once for each entry tblOutlays. For example, if I have two disbursements stored in tblOutlays, my query results in the following output:
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate],[Disbursement1]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate],[Disbursement1]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate],[Disbursement1]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate],[Disbursement2]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate],[Disbursement2]
[FileNumber],[ClientName], [Date], [FileNumber], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate],[Disbursement2]
As a result, my list of timesheet entries ends up looking like this:
[Date], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate]
[Date], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate]
[Date], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate]
[Date], [WorkDetail1], [Start1], [Finish1], [Employee], [Rate]
[Date], [WorkDetail2], [Start2], [Finish2], [Employee], [Rate]
[Date], [WorkDetail3], [Start3], [Finish3], [Employee], [Rate]
In addition, my calculations ends up doubling the value of the work performed.
So my first question is, how do I stop my timesheet rows being repeated in my invoice/report (and their values being summed more than once)?
My second problem/question, is how do I get the disbursements (ie the entries in tblOutlays) to list out on the first page (once only) whilst forcing the timesheet entries (ie the entries in tblTimesheets) to list out (again, once only) on the second and later pages?
TIA
Bob
