Report containing multiple critera/filters

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

This is what I need done, I've tried everything I know how, but it's not
enough.

I run departmental audits, of which contain multiple employees, and make
multiples purchases against their accounts. I run these reports monthly, and
sometimes need to revisit monthly reports where data has changed.

I have theses fields, with the needed criteria for the reports next to them.

department - name
# of employees reviewed per dept - count
employees reviewed - names listed, list preceded by "(C)"
review date - none
review type - if more than one, list them separated by "/"
activity period (billing cycles) - if more than one, list them separated by
","
$amt reviewed - sum
# of transactions reviewed - sum
reconciled by - names listed, list preceded by "(R)"
approved by - names listed, list preceded by "(A)"

employee table
employee_id
employee

accounts table (liked to employee table by employee_id)
account
employee_id
department
reconciled by
approved by

review table (liked to accounts table by account)
review_id
account
review date
review type
activity period
$ amt reviewed
# of transactions reviewed

Is this possible to achieve what I'm looking for?
 
I run departmental audits, of which contain multiple employees, and make
multiples purchases against their accounts.
Are you auditing accounts or employees? What has 'multiples purchases
against their accounts' to do with these audits as your tables do not have
fields for purchases?

Why is accounts table linked to employee table by employee_id?

Seems to me the review table linked to accounts table by account and
employee table by employee_id to show who did the audit.

Why does '$amt reviewed' matter?

The field '# of transactions reviewed' would not be 'sum' but 'count'.

What is the purpose of this data being collected?
 
I'm auditing spending habits of employees. I need to know how many purchases
(per department) were made, and the total $ amount of purchases. Why?
because that's what my Director wants.

Accounts table is linked to employee table by employee id because I have an
employee table with all the employee's info on it, like; name, address,
length of employment...etc. The account is driven by employee id because if
the account holder changes, all I have to do is punch in a different employee
id and all related information updates along with it.

I accept your correction on the '# of transactions reviewed'... I was in a
rush typing.

So... is this doable?
 
Try these queries --
Department level query --
SELECT [department], Count(SELECT [accounts].[account] FROM
[accounts].[account] GROUP BY [accounts].[account]) AS [# of employees
reviewed per dept],
Sum([$ amt reviewed]) AS [$ Amount], Sum([# of transactions reviewed]) AS
[Count of transactions]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department];

Employee level query --
SELECT [department], [employee], [accounts].[account], Sum([$ amt reviewed])
AS [$ Amount], Sum([# of transactions reviewed]) AS [Count of transactions],
[review type], [activity period], [reconciled by], [approved by]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department], [employee], [accounts].[account], [review type],
[activity period], [reconciled by], [approved by];
 
I tried the first set of code, but I got an error message about # of
"[employees reviewed per dept]"

Were you able to test this before you suggested it to me? If it worked for
you, I'd like to know where I'm going wrong.

KARL DEWEY said:
Try these queries --
Department level query --
SELECT [department], Count(SELECT [accounts].[account] FROM
[accounts].[account] GROUP BY [accounts].[account]) AS [# of employees
reviewed per dept],
Sum([$ amt reviewed]) AS [$ Amount], Sum([# of transactions reviewed]) AS
[Count of transactions]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department];

Employee level query --
SELECT [department], [employee], [accounts].[account], Sum([$ amt reviewed])
AS [$ Amount], Sum([# of transactions reviewed]) AS [Count of transactions],
[review type], [activity period], [reconciled by], [approved by]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department], [employee], [accounts].[account], [review type],
[activity period], [reconciled by], [approved by];

--
Build a little, test a little.


Mac said:
I'm auditing spending habits of employees. I need to know how many purchases
(per department) were made, and the total $ amount of purchases. Why?
because that's what my Director wants.

Accounts table is linked to employee table by employee id because I have an
employee table with all the employee's info on it, like; name, address,
length of employment...etc. The account is driven by employee id because if
the account holder changes, all I have to do is punch in a different employee
id and all related information updates along with it.

I accept your correction on the '# of transactions reviewed'... I was in a
rush typing.

So... is this doable?
Click to expand...
Click to expand...
 
There was a closing parenthesis missing. No I did not test.
Try it now --
SELECT [department], Count(SELECT [accounts].[account] FROM
[accounts].[account] GROUP BY [accounts].[account])) AS [# of employees
reviewed per dept], Sum([$ amt reviewed]) AS [$ Amount], Sum([# of
transactions reviewed]) AS [Count of transactions]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department];

--
Build a little, test a little.


Mac said:
I tried the first set of code, but I got an error message about # of
"[employees reviewed per dept]"

Were you able to test this before you suggested it to me? If it worked for
you, I'd like to know where I'm going wrong.

KARL DEWEY said:
Try these queries --
Department level query --
SELECT [department], Count(SELECT [accounts].[account] FROM
[accounts].[account] GROUP BY [accounts].[account]) AS [# of employees
reviewed per dept],
Sum([$ amt reviewed]) AS [$ Amount], Sum([# of transactions reviewed]) AS
[Count of transactions]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department];

Employee level query --
SELECT [department], [employee], [accounts].[account], Sum([$ amt reviewed])
AS [$ Amount], Sum([# of transactions reviewed]) AS [Count of transactions],
[review type], [activity period], [reconciled by], [approved by]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department], [employee], [accounts].[account], [review type],
[activity period], [reconciled by], [approved by];

--
Build a little, test a little.


Mac said:
I'm auditing spending habits of employees. I need to know how many purchases
(per department) were made, and the total $ amount of purchases. Why?
because that's what my Director wants.

Accounts table is linked to employee table by employee id because I have an
employee table with all the employee's info on it, like; name, address,
length of employment...etc. The account is driven by employee id because if
the account holder changes, all I have to do is punch in a different employee
id and all related information updates along with it.

I accept your correction on the '# of transactions reviewed'... I was in a
rush typing.

So... is this doable?

:

I run departmental audits, of which contain multiple employees, and make
multiples purchases against their accounts.
Are you auditing accounts or employees? What has 'multiples purchases
against their accounts' to do with these audits as your tables do not have
fields for purchases?

Why is accounts table linked to employee table by employee_id?

Seems to me the review table linked to accounts table by account and
employee table by employee_id to show who did the audit.

Why does '$amt reviewed' matter?

The field '# of transactions reviewed' would not be 'sum' but 'count'.

What is the purpose of this data being collected?

--
Build a little, test a little.


:

This is what I need done, I've tried everything I know how, but it's not
enough.

I run departmental audits, of which contain multiple employees, and make
multiples purchases against their accounts. I run these reports monthly, and
sometimes need to revisit monthly reports where data has changed.

I have theses fields, with the needed criteria for the reports next to them.

department - name
# of employees reviewed per dept - count
employees reviewed - names listed, list preceded by "(C)"
review date - none
review type - if more than one, list them separated by "/"
activity period (billing cycles) - if more than one, list them separated by
","
$amt reviewed - sum
# of transactions reviewed - sum
reconciled by - names listed, list preceded by "(R)"
approved by - names listed, list preceded by "(A)"

employee table
employee_id
employee

accounts table (liked to employee table by employee_id)
account
employee_id
department
reconciled by
approved by

review table (liked to accounts table by account)
review_id
account
review date
review type
activity period
$ amt reviewed
# of transactions reviewed

Is this possible to achieve what I'm looking for?
Click to expand...
Click to expand...
Click to expand...
 
It worked after I tweeked it a little, I kept getting an error.

Thanks for your help, if anything it lead me in the right direction.

KARL DEWEY said:
There was a closing parenthesis missing. No I did not test.
Try it now --
SELECT [department], Count(SELECT [accounts].[account] FROM
[accounts].[account] GROUP BY [accounts].[account])) AS [# of employees
reviewed per dept], Sum([$ amt reviewed]) AS [$ Amount], Sum([# of
transactions reviewed]) AS [Count of transactions]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department];

--
Build a little, test a little.


Mac said:
I tried the first set of code, but I got an error message about # of
"[employees reviewed per dept]"

Were you able to test this before you suggested it to me? If it worked for
you, I'd like to know where I'm going wrong.

KARL DEWEY said:
Try these queries --
Department level query --
SELECT [department], Count(SELECT [accounts].[account] FROM
[accounts].[account] GROUP BY [accounts].[account]) AS [# of employees
reviewed per dept],
Sum([$ amt reviewed]) AS [$ Amount], Sum([# of transactions reviewed]) AS
[Count of transactions]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department];

Employee level query --
SELECT [department], [employee], [accounts].[account], Sum([$ amt reviewed])
AS [$ Amount], Sum([# of transactions reviewed]) AS [Count of transactions],
[review type], [activity period], [reconciled by], [approved by]
FROM [employee] LEFT JOIN [accounts] ON [employee].[employee_id] =
[accounts].[employee_id] LEFT JOIN [review] ON [accounts].[account] =
[review].[account]
GROUP BY [department], [employee], [accounts].[account], [review type],
[activity period], [reconciled by], [approved by];

--
Build a little, test a little.


:

I'm auditing spending habits of employees. I need to know how many purchases
(per department) were made, and the total $ amount of purchases. Why?
because that's what my Director wants.

Accounts table is linked to employee table by employee id because I have an
employee table with all the employee's info on it, like; name, address,
length of employment...etc. The account is driven by employee id because if
the account holder changes, all I have to do is punch in a different employee
id and all related information updates along with it.

I accept your correction on the '# of transactions reviewed'... I was in a
rush typing.

So... is this doable?

:

I run departmental audits, of which contain multiple employees, and make
multiples purchases against their accounts.
Are you auditing accounts or employees? What has 'multiples purchases
against their accounts' to do with these audits as your tables do not have
fields for purchases?

Why is accounts table linked to employee table by employee_id?

Seems to me the review table linked to accounts table by account and
employee table by employee_id to show who did the audit.

Why does '$amt reviewed' matter?

The field '# of transactions reviewed' would not be 'sum' but 'count'.

What is the purpose of this data being collected?

--
Build a little, test a little.


:

This is what I need done, I've tried everything I know how, but it's not
enough.

I run departmental audits, of which contain multiple employees, and make
multiples purchases against their accounts. I run these reports monthly, and
sometimes need to revisit monthly reports where data has changed.

I have theses fields, with the needed criteria for the reports next to them.

department - name
# of employees reviewed per dept - count
employees reviewed - names listed, list preceded by "(C)"
review date - none
review type - if more than one, list them separated by "/"
activity period (billing cycles) - if more than one, list them separated by
","
$amt reviewed - sum
# of transactions reviewed - sum
reconciled by - names listed, list preceded by "(R)"
approved by - names listed, list preceded by "(A)"

employee table
employee_id
employee

accounts table (liked to employee table by employee_id)
account
employee_id
department
reconciled by
approved by

review table (liked to accounts table by account)
review_id
account
review date
review type
activity period
$ amt reviewed
# of transactions reviewed

Is this possible to achieve what I'm looking for?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top