Query Help

[Krzysztof via AccessMonster.com]

Good Afternoon!

I am in need of some help with a query i am doing. i cannot get what i am
looking for.

there is a table, sort of a transaction table, that i am working with. it has
a key field, which in this case pertains to the weeknumber of any given week.
then there are some job id's and then some numerical data, and i need to
track differences from week to week. i will explain further:

table:

Week | Job | Amount
44 1235 15000
44 1234 50000
45 1235 10000

etc...


so basically i can look at week 44 and determine that job 1235 dropped 5000
in week 44. also, in reality, job 1234 completed, so it dropped 50000. but,
since there is no amount present, the job does not make it on the list,
making it hard to grab that the job was completed.

is there a way i can get this without using several queries/tables?

i have tried with adding the same table in the query, but somewhere i am not
entering the correct criteria.

 

[Ed B]

Hi Krzysztof,

A couple of thoughts:

1. Could your application, whenever a job is finished, automatically add a
record that reflects the drop in the job to zero the week in which it is
finished? In other words, add a record like:

Week | Job | Amount
45 1234 0

2. Does your application _always_ add a record at the end of each week with
the "remaining amount" for each and every still-open job? If so, you might
be able to create a simple "totals" subquery that finds the "last week" of
every job such as this:

q1:
SELECT tblJobs.JobID, Max(tblJobs.WeekNumber) AS FinalWeek
FROM tblJobs
GROUP BY tblJobs. JobID;

Then, join that query (q1) back to tblJobs like this to create q2:

q2:
SELECT q1.JobID, q1.FinalWeek, tblJobs.Amount AS FinalAmount
FROM tblJobs INNER JOIN q1 ON (q1.FinalWeek = tblJobs.WeekNumber) AND
(tblJobs.JobID = q1.JobID);

[I hope I got all of that typing right... ]

when you run q2, you'll get a list of all jobs and the remaining job value
on their "final week." If you want only completed jobs, you'll have to add a
criteria that limits the "final week" to being, for example, prior to the
current week (using e.g. < Format(Date(),"ww")

Good luck,

Ed