Specific record selection based on dates

  • Thread starter Thread starter Nicole
  • Start date Start date
N

Nicole

I have a query that performs calculations based on a
table. These numbers will be updated periodically. How
do I have the query reference the correct entry. For
example:

I am calculating job costs based on overhead expenses.
Job 1 costs $500 based on the entries entered on 10/1,
but job 2 needs to be based on updated entries on 10/5.
But if those numbers are changed, it changes the
historical data of Job 1.
The query does not know what date to use. How do I
specify? A multiple entry in my overhead expenses causes
no calculation because of multiple entries. Help please.
 
Hi,


If historical data is required, you should consider to NEVER update old
information with new one, but ALWAYS append new information into a new
record.


StartingDate TaxRate
1-1-1900 7%
1-1-2002 7.5%


With that kind of data, if we have to check the accuracy of the tax for
a bill dated 1-1-2000, we know the tax rate to be used is 7%, but if the
bill is dated 1-1-2003, the tax rate to be used is 7.5%. The "date" (in
fact, the record) to be used is, in this example, defined by the context.

Your context is not well explained, it is difficult to follow and see
why the historical data seems required, but cannot be ... stored as
"multiple entries" (?)



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for the reply. I will try to be more specific.

Ok.. for example.

I have a jobtable
[JobDate]
[JobMiles]
[Client]...etc.

These are the daily inputs for jobs as they come in.

They want to calculate job cost based on expense and
desired profit. Example...

Annual Overhead Expenses
Driver's Pay (Depending on the driver driving the job)
Lease and Insurance payments on the trucks
Fuel cost
And a predetermined [BaseWeeklyMiles] to deterine cost
per mile.

All of these factors I currently have in the same table.
So if these numbers are input on 10/1/2003 that is fine
all jobs so far are calculated on these numbers. But as
time goes by and they update these numbers.. on 10/15 for
example I need a way to have the job records determine
whether to use the entries from 10/1 for 10/15. I need
to keep the information in 10/1 for historical data, but
want jobs after 10/15 to use the new numbers.

When I run a query to calculate them it pulls a record
for each CostFactor record. I am trying to avoid having
to enter the overhead, Truck Costs, etc... with every job
entered.
Any suggestions? Please help... I have to deliver to a
client ASAP and I only recently found the problem after
more job entries were entered. Thank You.
 
Hi,

I am not sure I understand completely, but it seems you want to find the
"latest" entry for each job?

If you have the data

JobDate JobMiles Client
1-1-1900 45 a
2-2-1900 56 a



Then, you can proceed in two queries. The first one would find the Max(
JobDate ), by Client:

SELECT Client, MAX(JobDate) As MaxJD
FROM myTable
GROUP BY Client


The second query will just join the original table with the query we just
produce:

SELECT myTable.*
FROM myTable INNER JOIN previousQuery
ON myTable.JobDate=previousQuery.MaxJD
AND
myTable.Client=previousQuery.Client



With that, the query will leave you with the latest record, for each client,
in our example, just with

2-2-1900 56 a



Is that in the lines of what you are looking for? If not, what is the
problem to "compute the overhead" when you have more than one record,
exactly (small example) ?


Hoping it may help,
Vanderghast, Access MVP




Nicole said:
Thanks for the reply. I will try to be more specific.

Ok.. for example.

I have a jobtable
[JobDate]
[JobMiles]
[Client]...etc.

These are the daily inputs for jobs as they come in.

They want to calculate job cost based on expense and
desired profit. Example...

Annual Overhead Expenses
Driver's Pay (Depending on the driver driving the job)
Lease and Insurance payments on the trucks
Fuel cost
And a predetermined [BaseWeeklyMiles] to deterine cost
per mile.

All of these factors I currently have in the same table.
So if these numbers are input on 10/1/2003 that is fine
all jobs so far are calculated on these numbers. But as
time goes by and they update these numbers.. on 10/15 for
example I need a way to have the job records determine
whether to use the entries from 10/1 for 10/15. I need
to keep the information in 10/1 for historical data, but
want jobs after 10/15 to use the new numbers.

When I run a query to calculate them it pulls a record
for each CostFactor record. I am trying to avoid having
to enter the overhead, Truck Costs, etc... with every job
entered.
Any suggestions? Please help... I have to deliver to a
client ASAP and I only recently found the problem after
more job entries were entered. Thank You.
-----Original Message-----
Hi,


If historical data is required, you should consider to NEVER update old
information with new one, but ALWAYS append new information into a new
record.


StartingDate TaxRate
1-1-1900 7%
1-1-2002 7.5%


With that kind of data, if we have to check the accuracy of the tax for
a bill dated 1-1-2000, we know the tax rate to be used is 7%, but if the
bill is dated 1-1-2003, the tax rate to be used is 7.5%. The "date" (in
fact, the record) to be used is, in this example, defined by the context.

Your context is not well explained, it is difficult to follow and see
why the historical data seems required, but cannot be ... stored as
"multiple entries" (?)



Hoping it may help,
Vanderghast, Access MVP



please.


.
Click to expand...
Click to expand...
 
Back
Top