Web Development



Access 2000 - Begineer question regarding queries

By megahertz ·
I have a simple Access 2000 database with (7) fields, which are Work Order#, Consultant, Item, Activity, Hours Worked & Client.

How do I create a query that will ask me what Work Order# do I wish to search for and after I enter the number, it will display all the records AND the total the number of hours worked.

My goal is to be able to print out the number of hours worked sorted either by item or activity or consultant.

I figured how to have it ask the question (enclose the text in [] but I cannot get it to summarize.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Well you can cheat, but that is actually two

by Tony Hopkinson In reply to Access 2000 - Begineer qu ...


Select * From MyWork Where Workorder = 1

Gets the detail

Select Sum([Hours Worked]) Where WorkOrder = 1

However there we run into problems when trying to do this in one query.

Basically you can do a join or a union, the queries aren't too hard but they just shift the problem to the display / report.

A join would give something like this,
Item Activity Consultant Hours Worked Total
1 2 1 1 3
2 1 2 2 3

I bet you don't want a report that looks like that !

A union

Item Activity Consultant Hours Worked
1 2 1 1
2 1 2 2
? ? ? 3

? = null which hopefully will display as blank

However Order By works on all the results, so as soon as you add that say order By Activity

Item Activity Consultant Hours Worked
? ? ? 3
2 1 2 2
1 2 1 1

oops !

Now instead of using nulls you could use big values so the total line is always last
say 99, but now

Item Activity Consultant Hours Worked
1 2 1 1
2 1 2 2
99 99 99 3


So you could get clever and do this

Sorder Item Activity Consultant Hours Worked
? 2 1 2 2
? 1 2 1 1
Total ? ? ? 3

Then order By becomes Order By Sorder,Activity

or Order By Sorder,Item

The above is

Select Convert(VarChar(5),'') as Sorder,
Activity, ....

From MyWork Where WorkOrder = 1
Select Null, Null, Null,Null,Null, Sum([Hours Worked]) Where Workorder=1
Order By Sorder, Item

Me I'd have a deep think about this one query thing.....


Related Discussions

Related Forums