Multiple Query Values In A Report

[dr4ke]

I currently have a table which shows a number of different columns, but for the purpose of simplicity those which I will be discussing in this post are 'MailboxSize' & 'Status'. In reality there is no 'Status' field, but a number of conditions allow me to see what status the mailbox is (I note this so people don't ask why I have multiple queries).

What I have is 5 queries which count the Mailbox Size by Status. I.e.

1) Active (number of; 100MB, 1GB, 5GB, 50GB, Unlimited)
2) Deleted (number of; 100MB, 1GB, 5GB, 50GB, Unlimited)
3) Disabled (number of; 100MB, 1GB, 5GB, 50GB, Unlimited)
4) Suspended (number of; 100MB, 1GB, 5GB, 50GB, Unlimited)

When I create a report I want to break the sections by Mailbox size, not status. I.e.

1) 100MB (Number of: Active, Deleted, Disabled, Suspended)
2) 1GB (Number of: Active, Deleted, Disabled, Suspended)
3) 5GB (Number of: Active, Deleted, Disabled, Suspended)
4) 50GB (Number of: Active, Deleted, Disabled, Suspended)
5) Unlimited (Number of: Active, Deleted, Disabled, Suspended)



I've tried starting from a 'Blank Report' and adding 'Labels' and building these through expression (i.e. "[SuspendedSizeCount]![CountofMailbox]" which doesn't work, neither does "[SuspendedSizeCount]![CountofMailbox] = 100MB" when building the 100MB report).

Is there not just a simple way to build a report through a Query View, so I can add exactly what queries I want and the Criteria?

Regards

[rpeare]

well from the sound of it you have 4 identical queries (one for active, one for deleted, one for disabled, one for suspended) and they all show exactly the same information (in terms of columns not necessarily content).

If that's the case the easiest thing for you to do with your current structure is to make a union query. Without seeing your current structure it'd be hard to give you syntax but let's say your queries are called

Qry_Active
Qry_Deleted
Qry_Disabled
Qry_Suspended

and the fields for each one of these queries are:

100MB 1GB 5GB 50GB Unlimited

The union query would be:

SELECT *, "Active" AS UserLabel FROM Qry_Active
UNION ALL
SELECT *, "Deleted" as UserLabel FROM Qry_Deleted
UNION ALL
SELECT *, "Disabled" as UserLabel FROM Qry_Disabled
UNION ALL
SELECT *, "Suspended" as UserLabel from Qry_Suspended

I'm not sure if I have the syntax 100% correct but you get the idea.

Personally I would not do this at all. I'd create one query that had the label calculated. As you said you have fields that you're using to determine what goes in which query you can use those same criteria to generate a label. For instance let's say you have field CANCEL_DATE and if that field is filled in they automatically qualify as DISABLED. Further let's say that you have a history of payment in a secondary table to your clients and anyone who has made a payment in the last three months is considered ACTIVE. These are all things you can calculate in on query without going through the hassle of a union query. The only time you should be considering a union query is when you have two different data sources that have enough in common that you want to try and homogenize the data. From the sound of it you've got one data source (your database).

[dr4ke]

Thank you for your reply; How do I calculate labels to then 'count' my values? It's not too easy to provide my DB as it has sensitive information in it...

But what I want to end up with is something like the below...

Mailbox Size	Active	Deleted	Disabled	Suspended
100MB	30	12	13	90
1GB	12	13	51	15
5GB	39	259	21	123
50GB	140	145	112	109
Unlimited	120	150	215	214

I have another question open in the Query section, asking a different question which could also solve this problem (https://www.accessforums.net/queries...ion-31821.html).

By the sounds of what you're saying above, I can create labels which will do the calculations and return me what I want... Will it count by mailbox size and status??

[rpeare]

you're basically making a matrix out of a normalized database.

so let's say I have this database

Mailbox_Size  Open_Date  Close_Date  On_Vacation
100MB         1/1/2013             
100MB         1/1/2012   12/31/2012
100MB         6/30/2012              Yes
1GB           1/1/2013             
1GB           1/1/2012               Yes
1GB           6/30/2012              Yes

Let's assume that anything with an open date is active as long as the ON_VACATION flag is not there, if the account is active but the ON_VACATION flag is YES then we're going to mark it as SUSPENDED. ANything with a CLOSE_DATE we're going to assume is deleted

You want your matrix to look like this

MailboxSize  Active  Deleted  Suspended
100MB        1       1        1
1GB          1                2

In Your query you'd have 4 columns, the first being your mailbox size.

The second would be the formula Active: iif(isnull(close_date) and on_vacation <> "yes"), 1, 0)
The third would be the formula Deleted: iif(not isnull(close_date), 1, 0)
The fourth would be the formula Suspended: iif(isnull(close_date) and on_vacation = "yes", 1, 0)

then you would change the query to an AGGREGATE query (look for the sigma button on your toolbar looks like an upper case E) and put SUM in each of the calculated fields as opposed to GROUP by.

That'll give you the direction to go.

[dr4ke]

Thank you for this; I think I need to have a couple more reads of what you've responded with but I'm very grateful for the suggestion and will definitely try and work it out!

Thank you again I'll let you know how it goes.

[dr4ke]

Hi,

i've got slightly stuck; In order to establish whether a mailbox is disconnected Table 1 ("MBXStats") needs to show an entry in Table 2 ("DisconnectedMBXs"). I.e. If Joe Bloggs (User ID: BloggyBoy) has an entry in 'DisconnectedMBXs' then he is disconnected...Is there a way to write a query to say "If USERID is in DisconnectedMBXs then TRUE else FALSE"?

This way if I try and link the two tables it would remove the rest of the entries that aren't in disconnectedMBX's?

[rpeare]

as long as the field is common to both tables you can link it in your query and test to see if it exists in your secondary table by using something like

iif(not isnull([MBXSTATS FIELD NAME HERE]) AND not isnull([DISCONNECTEDMBXs FOREIGN KEY FIELD])), 1, 0)

this is basically saying if BOTH the field you in your table MBXStats table AND the FK field in your table DisconnectedMBXs are populated, the mailbox is disconnected, otherwise it's not counted in the disconnected total)

[dr4ke]

Thanks, the field is not common unfortunately so I have done it this way...

First I brought both tables together

 SELECT *FROM allmailboxstats LEFT JOIN disconnectedmailboxes ON allmailboxstats.displayname=disconnectedmailboxes.displayname;

Then I did the working out as you have suggested

 SELECT FullQueryTest.allmailboxstats.ID, FullQueryTest.allmailboxstats.DisplayName, FullQueryTest.OU, OULookup.[Business Unit], FullQueryTest.maxsendsize, FullQueryTest.prohibitsendreceivequota, FullQueryTest.HiddenFromAddressListsEnabled, FullQueryTest.IsValid, FullQueryTest.DisconnectDate, FullQueryTest.DisconnectReason, IIf(IsNull(DisconnectDate) And ((maxsendsize)<>"0 B (0 bytes)") And ((HiddenFromAddressListsEnabled)=No),1,0) AS Active, IIf(IsNull(DisconnectDate) And ((maxsendsize)="0 B (0 bytes)"),1,0) AS Disabled, IIf(IsNull(DisconnectDate) And ((allmailboxstats.HiddenFromAddressListsEnabled)=Yes) And ((allmailboxstats.maxsendsize)<>"0 B (0 bytes)"),1,0) AS Suspended, IIf(Not IsNull(DisconnectDate) And (((DisconnectDate)>=Now()-210) AND ((IsValid)=Yes)),1,0) AS Deleted
FROM (FullQueryTest INNER JOIN MailboxSizes ON FullQueryTest.prohibitsendreceivequota=MailboxSizes.Mailboxes) INNER JOIN OULookup ON FullQueryTest.OU=OULookup.OU;

The only problem is that there are duplicates, but I assume that's because the displayname is duplicated in the second table so therefore shows up twice in the query?

[rpeare]

I don't know what your data structure looks like, if you have a one to many relationship between two of your datasets then you'll have to aggregate the MANY side of the relationship into one record (perhaps choosing the most recent record for instance)

[dr4ke]

How do i do that?

[rpeare]

as I said I don't know your table structure or how you are going to choose the record that you want. I'd have to know more about it to answer yours directly but here's an example


Let's say I had the following two tables:

Tbl_Main
Main_PK  Field1  Field2 ---> other fields
1        AAAAAA  BBBBBB
2        CCCCCC  DDDDD

Tbl_Sub
Sub_PK  Sub_Date  Main_PK ----> other fields
1       1/1/2013  1
2       1/5/2013  1
3       1/3/2013  2

What you want to end up with is something like this

Field1  Field2  Sub_Date
AAAAAA  BBBBBB  1/5/2013
CCCCCC  DDDDDD  1/3/2013

So the first step would be to build the query

Qry_Sub
SELECT Main_PK, Max(Sub_Date) as MaxDate FROM Tbl_Sub

This would give you the maximum sub_date for each Main_PK value.

Then you'd create a query based on your Tbl_Main and the Qry_Sub and link them through the Main_PK