Help with query for financial report

[iProRyan]

Hi Guys,



Just coming to the end of a project but having some issues with a query that will be used to produce a financial report and wondered if anyone could help.

Basic Info:
Four Table - tblCustomer, tblJobParts, tblJobs, tblParts

What am I trying to do is get it to show the Quantity from tblJblParts, CostToBuy from tblParts, CostToCustomer from tblParts and then in the report I will calculate the amount spent and profit etc, but I want the user to be able to choose the start and end date, and in the report for things like Labour Charge I don't want to include the cost to buy in the expenses as CostToCustomer is a calculated, is this possible? If not I have an idea of a work around. But at this moment in time I'm a bit lost of where to start, could someone provide me with some advice.

Thanks,
Ryan

[orange]

You are going to have to show readers a jpg of your tables and relationships.

Can you post a copy of your database with sufficient data to test. Remove anything confidential first.

[iProRyan]

I will work on removing the confidential data and adding some sample data, but at the moment here's a selection of screenshots for my table structure and relationships.

[orange]

Good stuff. A quick comment you might consider including a field in your tblJobParts for the UnitSellingPrice or similar name. This records the Price that this Part was sold for on this Job. If you only get your selling price from the Parts table you will have trouble with existing JobPart prices if and when you change the value in the Parts table.

It also appears that you have not shown a tblPartCategory nor a tblSupplier.
Also your CostToBuy in tblParts should have some sort of Date associated with it.

[iProRyan]

My database isn't that advanced, simply because it does not need to be at the moment, should it need to I can always add that later or just add categories to the Lookup List as and when necessary.
I have now added a UnitSellingPrice as I completely forgot to take into account possible price changes.

Any ideas of how I will get my query to work?

This is for an A2 ICT Project (Yes, we are allowed help) so I can talk about that as a possible update in my review! :P

[orange]

Starting query based on 1st post

SELECT
tblJobParts.Quantity
,tblParts.CostToBuy
,tblParts.CostToCustomer
FROM
tblJobParts INNER JOIN tblParts ON
tblJobParts.PartId = tblParts.PartId

[iProRyan]

That works perfect! Think I will change CostToCustomer to UnitSellingPrice through, and now any ideas of how I could incorporate the JobDate in their so it's between [Enter Start Date: (DD/MM/YYYY)] and [Enter End Date: (DD/MM/YYYY)]

[orange]

If you change CostToCustomer to UnitSellingPrice, then make sure that you interpret Quantity as NumberOfUnits.
You need to calculate UnitSellingPrice * NumberOfUnits to get a Total Selling Price( which you always calculate via query and NEVER store in a Table).

What do you think should be done in order to display JobDate? In which table is JobDate?
What tables are in your current query?
Show us what you think the query should look like.

Use the query designer -- in design view and sql view to see what you are dealing with.

Does your relationship diagram allow you to have a Customer without a Job?

[iProRyan]

You can have a customer without a job but it doesn't really do anything haha!

Yeah to calculate the Total I'm just going to to have a label that adds up all the other totals which will be UnitCost*Quantity.

What I think I need to do is add to the select statement tblJobs.JobDate and add to the WHERE statement WHERE JobDate <= StartDate and >= EndDate it's getting that from the user I'm unsure about.

[orange]

If you used the SQL I gave you before.


go to query designer, sql view and paste that sql
then go to the design view
add the jobs table
add the jobdate

and see how it goes.

[iProRyan]

Right I have now amended my query (I do understand SQL as a language so feel free to use it) to include the JobDate from tblJobs but can't quite get the Date to work.

In the criteria for the date column I have tried "<=Date()-30 And >=Date()" and "<=[Enter Start Date] And >=[Ender End Date]" (without the quotes) but no data is found when there is data that should be. I entered the data in DD/MM/YYYY format as that is how it is stored in the table.

Any ideas why this is happening?

[orange]

Show the query SQL as it is now; and what criteria do you want to use.

I think this would work -needs syntax check
JobDate Between [EnterStartDate] AND [EnterEndDate]

[iProRyan]

Works perfectly! I can now add any other fields I want, thank you very much for all your help!

For being a Computing student as well and having to know SQL I probably should have got that myself haha but thank you orange!

Edit:
If you're feeling nice, I'm having another issue with a different query so I've posted under the queries section or you can find it here