create query to combine two tables together

[Associates]

Hi,

I was wondering if i could get some help with Access report. I have been
trying to figure out a solution for hours but to no avail.

I have one table for work_timesheet and the other table for work_invoices.
The following is the details of both tables

work_timesheet
------------------
TS_ID
StaffID
WorkDate
EntryDate
JobNo
Rate
TimeTaken

work_invoices
----------------
Inv_ID
Inv_No
Inv_Date - Date when invoice is generated
JobNo
Inv_Line1 - Description of invoice
Inv_Amount1 - $ amount associated with Line1
Inv_Line2 - Description of invoice
Inv_Amount2 - associated with Line2

Now, i was trying to generate a report that would present amount for the
COST and the BILLED for a certain period of time. To get the COST, i need the
table work_timesheet. And i need table invoices to know how much we bill our
clients.

If i create a relationship between timesheet and invoice based on JobNo, i
got a very huge amount which is not correct. I don't think i can use JobNo as
the field to connect them together. I was thinking of creating another table
that would hook these two tables together but don't know how to go about that.

Thank you in advance

 

[Bob Barrows [MVP]]

Hi,

I was wondering if i could get some help with Access report. I have
been trying to figure out a solution for hours but to no avail.

I have one table for work_timesheet and the other table for
work_invoices. The following is the details of both tables

work_timesheet

In the future, please supply data types as well as field names when you are
being so helpful as to provide schema details like this.
It really reduces the amount of clarification we need to ask for.

StaffID
WorkDate
EntryDate
JobNo
Rate
TimeTaken

work_invoices
----------------
Inv_ID
Inv_No
Inv_Date - Date when invoice is generated
JobNo
Inv_Line1 - Description of invoice
Inv_Amount1 - $ amount associated with Line1
Inv_Line2 - Description of invoice
Inv_Amount2 - associated with Line2

Nothing to do with your problem, but you are going to regret this design
choice when they decide to add a line 3, or 4, or 5 ...
Typical invoice table design involves two tables: a header table and a
details (line items) table, linked by Inv_No.

Each line of an invoice is kept in a separate record, identified by a line
number in a field called, well ... how about LineNumber?

Now, i was trying to generate a report that would present amount for
the
COST and the BILLED for a certain period of time. To get the COST, i
need the table work_timesheet. And i need table invoices to know how
much we bill our clients.

If i create a relationship between timesheet and invoice based on
JobNo, i got a very huge amount which is not correct. I don't think i
can use JobNo as the field to connect them together. I was thinking
of creating another table that would hook these two tables together
but don't know how to go about that.

Well it sounds as if you need to use more than JobNo to link the two tables,
but without understanding the relationship between the two tables, it is
impossible to explain exactly how to create that link. Which date field in
timesheet is related to the Inv_Date field?

 

[John Spencer]

Two query solution. I'm not sure how you calculate the amount so you will
need to develop the correct formula

First query gets the total costs by job for a specified period
SELECT JobNo,
Sum (Rate * TimeTaken) as TotalCost
FROM Work_Timesheet
WHERE WorkDate Between #1/1/2008# and #1/31/2008#
GROUP BY JobNo

Second query, uses that query and the Work_Invoices table
SELECT Work_Invoices.*, Q.TotalCost
FROM Work_Invoices LEFT JOIN QueryAbove as Q
ON Work_Invoices.JobNo = Q.JobNo

That could be all done in one query since your field and table names conform
to good naming practices

SELECT Work_Invoices.*, Q.TotalCost
FROM Work_Invoices LEFT JOIN
(SELECT JobNo,
Sum (Rate * TimeTaken) as TotalCost
FROM Work_Timesheet
WHERE WorkDate Between #1/1/2008# and #1/31/2008#
GROUP BY JobNo) as Q
ON Work_Invoices.JobNo = Q.JobNo


Optionally, a DSum function in the Select clause or a subquery in the select
clause.

SELECT Work_Invoices.*
, DSum("Rate * TimeTaken","Work_TimeSheet","JobNo=" & Chr(34) &
Work_Invoices.JobNo & chr(34) & " AND WorkDate between #1/1/2008# and
#1/31/2008#") as Billed
FROM Work_Invoices

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County