Report and or Query problem that is driving me up the wall.

[Christopherusly]

Introducing the tables and structure to start with, three tables - the common element between them all is the Job No/Project Code, for example CO00507144.

Table - Finance.

Attr: Project Manager
Field2
Job NO
Job Title
Network Activity
Work Stage
Employee No
Staff Member
Amey Fiscal Week Year
HR
£
£ / HR

Table - Job Hours

Project Code
Job Title
Staff Member
Hours

Table - Job Details

Project Code
Service Area
Project Title
Work Stage
Project Director
Project Manager
Start Date
Completion Date
Gateway Date

What i need to be able to do is run a report which pulls information from the Finance table and Job hours table to enable a comparison of fee estimate against fee spend.

- Just to explain, for Job NO (in the Finance table), there will be multiple records for the same job number in the Finance table, each one represents a member of staff booking time to a design job.

For Job Code (in the job hours table) there will be multiple records for the same job number, each one represents the time allocated to induviduals to complete their area of work on the project.

My problem comes in pulling them all together. I attach for your information, a MiniWIP report which shows what i am trying to achieve but falls short of the mark, the problem being i want a SUM for each job of the spend from the finance table and the total estimated fee, whats happening instead, as you will see with the Nyton Road example is that i am getting two lines for the job not one.

I think i need a many to many relationship on the tables to enable me to run the query to produce the report, but i am stumped at how this actually works feeling very foolish about the whole affair as i did not think i was doing too badly, seems i was wrong.

Please find that i have attached a sample of my database along with a report sample of what i am trying to do, feeling so close, yet so far away from the answer, any support or suggestions would be most welcome.

If you need more information please let me know.

Thanks guys !

Christophe

 

[PNGBill]

Study your Tables.

Names should be Unique and certainly, Primary Keys must be if your first fields are pk's

You have two tables with the same field name (as pk's?)

TableFinance will have any number of Unique Records all must have their own Unique Primary Key. Often an Auto Number field that is never used in reports etc is used to keep the records "Unique" Imagine all the John Smiths, They all have a unique Primary Key so even if they appear to be the same, they are not.

TableJobHours

Again this needs it's own PK

TableJobDetails

Same again with PK

Think about JobHours and JobDetails... They probably both should not link to ProjectCode.

An Invoice has TblInvoiceDetails and TblInvoiceHeader.
Invoice Header will include a field, CustomerID which will be the the primary Key of the TblCustomer but in this instance it will just be a normal field in TblInvoiceHeader.
TblInvoiceDetail will have a field InvoiceID which will be the link to TblInvoiceHeader. No need to have the CustomerID field included in TblInvoiceDetail.

In this scenario, most of your tables will be One to Many.

TblCustomer will be One to Many with TblInvoiceHeader which in turn will be One to many with TblInvoiceDetail.

Trust this assists

When you are satisfied with your tables, supply some specific field names and get assistance with a query.

 

[Christopherusly]

I think i got you on this one Bill, how does this look ? or have i completely missed the point ? - See attached relationships and fields.

many thanks for taking the time to comment on my question, it is hugely appreciated.

Christophe

 

[PNGBill]

Why two tbl_jobhours? They look to be the same.

Consider removing the join between tbl_jobhours and tbl_finance and join tbl_jobhours to tbl_projectdetails

If you then remove the 2nd tbl_jobhours(_1) your relationships will look better.
I say "look" because the real test is what the fields represent.

If All JobHours relate to a Project then only join JobHours to projects.

On a Family Tree, Your Children are linked to your cousins through You. If there is a line between one of your Children and a cousin or their Children,

Finance refer to JobNo. ProjectDetails refer to ProjectCode. No problems with different names if they are the same thing but it is easier to have similar, if not the same names.
tbl_finance - Shouldn't use ID as a field Name. Try FinanceID

What is your Main Table??

Is this a Projects database or a Business database that includes Projects??

It still looks a little off - I will study your first post again and get back to you.

tbl_graderates, if this is a reference to charge rates for different StaffMembers, Then consider. How many different charges does one staff member have ? If the answer is One then have that rate in the StaffMember table. John Smith has $200 in his record.

If you want to have this a little smarter then have a tblChargeRate and this has fields - Grade, Rate and in the staff table each staff member has a grade linked to the tblChargeRate.

To change the charge out rates, change the values of each of the rates in tblChargeRate and Wala! your next invoice will have the new rate for John Smith. But tblChargeRate will not join to any of the tables you show on the .pdf relationship as it will only join to tblStaff which will join one of the tables in your .pdf

 

[PNGBill]

Your Operation has a Finance Dept - Lets assume this is like an Accounting Business.

Whatever Other tables you have lets assume they are ok and work.

When an Accountant allocates his time each day he or she enters the ClientID and JobID along with the Hours (or Part Hours).

This information is held in the tblChargeOut and at the end of each month Invoices are generated by way of a query that will search tblChargeOut and join this information to tblClient and tblStaff and tblChargeRate.

tblStaff will have fields for StaffID, FirstName, LastName, StaffPosition (Linked to tblPositions )ne Staff to Many Positions), ChargeRateCode (May have another table for Charge Rates) and other info that is unique to each Staff Member.

tblClient will have ClienID, ClientName, ClientStaffID (linked to tblStaff - the staff member responsible for the Client) and other unique Client fields.

tblJobs will have JobID, ClientID (linked to tblClient) StaffID (the staff member responsible for the Job) JobStartDate, JobEndDate (maybe), JobDescription and other unique fields to each Job

tblChargeOut will have ChargeOutID, ChargeDate, StaffID, ClientID, JobID (linked to tblJob), ChargeTime (may have startTime and EndTime or Just ChargeTime) and any fields unique to this occurrence of a charge eg ChargeMemo)

Any Admin work will be charged to ClientID for Admin and JobID for Admin.

You may see that in your Relationships there is just one line between tblJob and tblClient - One Client to Many Jobs.

One link between tblClient and tblStaff is One Staff to Many Clients.

One link between tblStaff and tblChargeOut is One Staff to Many Charge Outs

One link between tblJob and tblChargeOut is One Job to Many Charge Outs.

No need for any other relationship links.

When you do your queries, you define the join as required for the query purpose.


Of course, tblStaff will have lines between tblPosition and some other tables but as far as the Jobs issue goes their only needs to be one way to connect the Staff Member to a Job or Client, or tblChargeOut, not needed for all.

This means that any Staff Member can charge any Clients Job it doesn't need to be the "managing" staff member.

Your query will select all records for selected date range, from tblChargeOut and join the StaffID to the ChargeRate by way of the StaffChargeCode and use the Hours on the tblChargeOut record and the Rate on the tblChargerate to calculate the cost of each occurrence to be charged to the Client.

Trust this makes sence.