Query/Report problem

[lsgls]

I have database in Microsoft Access which I would like to extend and use as an analysis tool.

The database currently holds wages data and invoice data, to enable us to extract information to correctly pay our employees and to find out the running costs of our jobs.

Each week, the hours worked by an employee are input. tblEmployees contains data of the employee and wage rate, tblJob contains data of the job and tblCostCode holds data of the relevent costcode ( cost codes are used both with invoices and wages, they are a list of codes which identify each stage in the projects : for example : we are a building company therefore each week an employee will be working on a specific part of the build, e.g. no. 300 Brickwork Shell and inevitable the invoices will also reflect this. )

These cost codes are very important to enable us to do a correct cashflow forecast of new jobs.

i want a report to show, each cost code and the total materials and wages for that cost code. but this must be job specific.

i have attmpted several queries to enable this but to no avail.

if anyone has any ideas, it would be much appreciated.

i understand that my query is the first step then my report will have to show only the totals for each code.

i want all codes to be displayed on one report.

 

[OhioSteve]

Perhaps you should post the structure of the tables. Here is an example.

table name: CARS
carID, number, pk
carName, text(50)
carColorID, text(50),fk(to carColors.ID)
isConvertible, boolean

"pk" stands for "primary key" and "fk" stands for "foreign key".

After posting the tables' structures, describe the report you want to make.

 

[lsgls]

tblCostCode
CCId, Text, pk
CostCode, Text

tblJobs
JobId, AutoNumber, pk
JobName, Text
JobAddress, Text

tblWeeks
WeekNo, Number, pk
Date, Date/Time

tblSuppliers
SuppId, AutoNumber, pk
SupplierName, Text

tblEmployees
EmpId, AutoNumber, pk
Surname, Text
Forename, Text
WageRate, Currency

tblProjects
Id, Autonumber, pk
JobId, Number, fk
EmpId, Number, fk
CCId, Text, fk
WeekNo, Number, fk
Hours, Number

tblInvoices
InvId, Autonumber, pk
JobId, Number, fk
SuppId, Number, fk
WeekNo, Number, fk
CCId, Text, fk
InvoiceTotal, Currency

Now I want a report which looks like this:

Job : JobName

CostCodeName1
Invoices : £xxxxxx
Wages : £xxxxx

CostCodeName2
Invoices : £xxxxxx
Wages : £xxxxx

etc..

The wages is a calculation of WageRate x Hours / 8

Any help appreciated, thanks

 

[lsgls]

Any help on this query would be much appreciated because i have still been unable to find a solution myself.

thanks