Collating data from two tables to make a single report 1

[CapnOats]

Right here goes...

I have two tables, one named job, one named work.

The job table describes the jobs gving them a unique reference called fmRef. The each record in the work table lists the designer that worked on the job, the job's ref (fmRef) and the hours worked.

The problem is, is that more than one designer can work on a single job.

What I need is to be able to make a report that goes through the Job table and for every record find all the hours worked in the work table, tally them up and display them as if they were simply another field in the table.

a sort-of-pseudo-code is

for each record in "job" find and total all matching "fmRef"s in "work"

id imagine it would probably have to go in part of the query fields or such, but having never dabbled in getting queries from more than one table im a bit stumped.

Any help would be greatly appreciated,
Thanks in advance,
Mike Coats

 

[CapnOats]

Right, i kinda got it working using

SELECT SUM(Work.[Hours Worked]) AS [Hours Worked]
FROM Job,Work
WHERE Job.[FM Reference] = Work.[FM Reference] AND Job.Customer = "Some customer"
GROUP BY Job.[FM Reference]

only problem now is whenever I try to select anything else it goves me an error about not including it as part of an aggregate expression.

now - ive looked in the help and i cant see how i can include it in the aggregate function as its definition is SUM(expr) with only one variable.

If anyone could shed some light on the situation it would be most appreciated.

Thanks in advance,
Mike Coats

 

[lespaul]

for each field you add in the select you have to add to the group by:

SELECT FIELD1, FIELD2, SUM(FIELD3) FROM TABLENAME GROUP BY FIELD1, FIELD2

HTH

Leslie

 

[CapnOats]

Yeah, that works brilliantly. Strange that i couldnt find any note of it anywhere - the built in help isnt really all that helpful. Thankfully there are people like you around to sort us out.

Regards,
Mike

 

[lespaul]

No problem! Glad to help,

leslie

 

[CapnOats]

hopefully, you can provide some more assistance,

im now trying to get it to display the previous query but between two dates - ive tried using both

WHERE ...
AND Job.[Assigned Date] >= "01/03/04"
AND Job.[Assigned Date] < "01/04/04"

and

WHERE ...
AND BETWEEN "01/03/04" AND "01/04"04"

but to no avail, it keeps telling me that ive type mismatches. the same happens if i use single quotes or double quotes, and if use # like #01/03/04# it doesnt complain, but instead returns no results.

Hopefully someone can shed somelight on the situation.

Thanks in advance,
Mike Coats

 

[lespaul]

dates have to have the # delimiter.

Is your field in the table a date? Does your date field in the table include times? then you need to either exclude the time portion from the search or include the time portion in the criteria.



Leslie

 

[CapnOats]

The field is set to "Date/Time" with the Format being "Short Date"

I would have thought that I would just have to include the date but do you mean to input it similar to the "General Date" format: "01/04/04 17:20:32" and search for after midnight on first date and mefore midnight on second date?

I thought that setting it to "Short Date" would simplify things, but who knows.

Thanks again,
Mike

 

[CapnOats]

its ok, i worked out the problem.

access's SQL seems to like its dates formatted in the mm/dd/yy format as opposed to the uk dd/mm/yy that access has let me set up its date format to be.

ive just got to hack together a workaround and itll all be gravy.

Thanks,
Mike

 

[lespaul]

glad you figured it out! I was stumped, always forget about all you europeans who store the date backwards!! HAHA

Leslie

 

[CapnOats]

Yeah in the end i just used three text boxes on the search form and concat'd the date as a long interger like yyyymmdd.

Now its easy peasy to fiddle with and search n sort.

Thankfully, im still working on a developement database with no real data in it or converting it all would be a royal pita.

Thanks very much for all the help.

Regards,
Mike