Multiple Queries to one

[straybullet]

Departments have different uses for the same data. We have a single table into which users are entering information about data they need - in the following layout:

FieldName RequiredValue CritcalValue
Name1 2 4
Name2 1 2
Name1 2 2
Name3 1 1
Name2 1 2

In other words, the same data has a different value to each. Using separate queries, I am able to give each field a value depending on the number of times it's been entered, the level of RequiredValue and the level of CriticalValue.

What Id really like to do is have the information in a single query (including the calculated values) similar to:

FieldName RequiredCritical RequiredNonCritical
Name1 6 10
Name2 17 14
Name3 12 8

Is there a way to do this with the 6 queries I have?


Let them hate - so long as they fear... Lucius Accius

 

[ZmrAbdulla]

I am not sure I understand your post.
But as per understanding..
There are functions like
DSum(), DCount(), GroupBy etc.. will be the way to go.
Don't use the existing queries for now, use the table to create a new query.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[straybullet]

Thank you for your response. I figured I hadn't explained well lol...

Monday am, I'll try your suggestion.

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

You simply wanted an aggregate query ?
SELECT FieldName, Sum(RequiredCritical) AS Critical, Sum(RequiredNonCritical) AS nonCritical
From yourTable
GROUP BY fieldName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[straybullet]

Thank you too!

Im going to try that using the formulas we came up with.

Basically, it's (# of times a field is named multiplied by the value for each level). Then the results are used in a second formula:

(Sum of the values above)/(100/(16*3) for fields deemed Required

and

(Sum of the values above)/(100/(16*3*2) for fields deemed Desired



Let them hate - so long as they fear... Lucius Accius