Query with

[Emmanuel Obu-Asare]

Hello,

I am trying to create a query from a table that will calculate fields whereby different date parameters will be used for different fields/columns. For instance 1st column is to calculate a field with a date range. the 2nd column will calculate a field with respect to a previous date and 3rd column will calculate from all the records regardless of the date. Please help me achieve this.

Thank you!

 

[theDBguy]

Hi. Welcome to AWF!

Are you talking about using a parameter query where the user will enter the dates at runtime?

 

[plog]

Please provide sample data. We need 2 sets:

A. STarting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us the data you expect to end up with when you feed the query the data from A.

Again, 2 sets of data.

 

[Emmanuel Obu-Asare]

Hi,
Thank you for your quick response:
There is a date column for the table. The parameters will be passed to the query through a form (DateFrom for the beginning date and DateTo for the end date).

 

[Emmanuel Obu-Asare]

Hello again,

I attached the sample DB and an image of the intended report. Kindly see it.

 

[plog]

Your expected results image (B data) shows a [REG NO] with a value of 'GM 9230', there is no such value in your database (A data) therefore your data doesn't conform to what I requested.

Please provide me expected results (B data) that is based on the data in your database (A data)

 

[Emmanuel Obu-Asare]

Yes I know. I was just trying to let you understand the scenario. In the image, I put alphabets underneath the column names for you to see how the calculation is done.

Hope you understand.

 

[plog]

I don't want to imagine anything, please make your data tie together. Show me the results you expect based on the data in your database.

 

[Emmanuel Obu-Asare]

I don't want to imagine anything, please make your data tie together. Show me the results you expect based on the data in your database.

Please I updated the query (see attached). Vehicle Recreational report. I run it with dates between 1st-30th June 2020. The image has the intended results. thanks for your help.

 

[plog]

The below SQL will produce the expected results:

SELECT Vehicle.RegNo, Vehicle.FixedRecreationKm, MonthlyLogs.ActualRecreationKM, [FixedRecreationKm]-[ActualRecreationKM] AS Variance, Vehicle.StartingVariance, [StartingVariance]+[FixedRecreationkm]-[ActualRecreationKm] AS CummVAriance, MonthlyLogs.workkm, MonthlyLogs.otherkm, MonthlyLogs.totalkm
FROM Vehicle INNER JOIN MonthlyLogs ON Vehicle.RegNo = MonthlyLogs.RegNo
WHERE (((MonthlyLogs.Period)=#6/9/2020#));

If it doesn't produce the results you expect on other datasets, provide more sample data (A & B) to demonstrate.

 

[Emmanuel Obu-Asare]

Thank you.
Your query does not include field for the variance of the previous month. The calculations for the Cummulative variance is not correct if I select a different date please.

 

[plog]

Please provide sample data to demonstrate