Reporting counts in another query...maybe?

[Geewaagh]

Adding up unique values in column
I have a single table with the following data:
Name _____Mon_____Tue_____Wed_____Thu_____Fri
Smith______ SF________________SF______________ SF
Doe________________MD______MD______________MD
Jones_______SF______SF________________SF________


Carl________________MD_______________________MD

I am trying to make a query or report that says
Staff_______Mon_____Tue_____Wed_____Thu_____Fri
sf__________ 2_______ 1________1_______ 1_______1
md_________ 0_______ 2________1_______0_______ 2

I have no clue on how to do this. Any suggestions?

Greg

[TheShabz]

I think you're better off exporting into Excel and using a VLookup

[Jim Doherty]

Assuming a table called tblStaff with field names........SName,Mon,Tue,Wed,Thu,Fri

then the SQL required for reproducing your goal (which you can paste into the SQL window) in query design is.....

 
SELECT s.Staff,
(SELECT Count(tblStaff.Mon) AS Mon FROM tblStaff WHERE (((tblStaff.Mon)=s.Staff))) AS Mon,
(SELECT Count(tblStaff.Tue) AS Tue FROM tblStaff WHERE (((tblStaff.Tue)=s.Staff))) AS Tue,
(SELECT Count(tblStaff.Wed) AS Wed FROM tblStaff WHERE (((tblStaff.Wed)=s.Staff))) AS Wed,
(SELECT Count(tblStaff.Thu) AS Thu FROM tblStaff WHERE (((tblStaff.Thu)=s.Staff))) AS Thu,
(SELECT Count(tblStaff.Fri) AS Fri FROM tblStaff WHERE (((tblStaff.Fri)=s.Staff))) AS Fri
FROM
[SELECT DISTINCT tblStaff.Mon as Staff FROM tblStaff WHERE (((tblStaff.Mon) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Tue as Staff FROM tblStaff WHERE (((tblStaff.Tue) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Wed as Staff FROM tblStaff WHERE (((tblStaff.Wed) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Thu as Staff FROM tblStaff WHERE (((tblStaff.Thu) Is Not Null))
UNION
SELECT DISTINCT tblStaff.Fri as Staff FROM tblStaff WHERE (((tblStaff.Fri) Is Not Null))]. AS s
ORDER BY s.Staff DESC;

What this SQL is doing by way of explanation is using correlated sub queries in tandem with the UNION query. The union query firstly selects distinct instances of each value (SF, MD or any other value for that matter) for each day of the week and presents this as a column aliased to the name of 'Staff'. We then have Six columns of data The first column is the 'Staff' column derived from the correlated sub query followed by five columns raised by declaring separate SELECT COUNT sql statements that each represent a day of the week Monday to Friday and each of which has its criteria clause set to equal the value found in any of the data values that appear in the rows for the 'Staff' column.

Hope this helps you although I tend to agree with the earlier post you seem to be using a table like a spreadsheet

Regards

[Geewaagh]

Jim,

Thanks for the reponse. Although you suggestion works (thanks!) it will not work for my application.

It might not be good practice, but my field name changes every week. So, instead of Mon, its Mon-7. When I do the schedule again, I rename the field to Mon-14, etc.

Maybe I am going about this wrong. Can I just add up unique values in my current qurey?

I know I can do a count on the Mon-7 column, but it will add up all my staff.

How do I tell access to count up just "SF" in a sum? I could see putting all my staff counts at the bottom of the footer for the weekly report if I knew how to do this.

Thanks again
Greg

[Jim Doherty]

You are welcome to the contribution which is specific to your posted question and as you pointed out works, but as I 'kind of guessed' it is NOT the solution if you are going to be changing field names and altering your table about and treatig it like a spreadsheet. SQL is specific as you can see

Use a spreadsheet! databases are designed to behave in a certain way as with all programs and as you can see the SQL required to make the thing do one thing alone is overkill, not generic enough.

Yes I could say look at DCount Aggregate functionality in Access but from what you are doing altering field names and working in a spreadsheet type way all I think it serves to do is push you along a road best served with a different program.

The design process of a 'database' certainly does not follow your current work flow methods I am afraid

[Geewaagh]

Jim,

Actually, I moved from excel to Access and it has help my world a lot! I have over 300 clients seen by a staff of 15. Data sorting, queries of clients for just one staff memeber, service type sorting, etc. has made all the difference in the world.

The only real "non-orthodox" thing that is done is renaming my days of the week to days+date is my only compromise. Excel might count fields but this is a very small part of the bigger picture.

The ease of use for the data sort, filtering, and reporting....I will never take this back to excel. Access is the right product.

Anyway, maybe this thread is now ill named. Should I start a new thread for the counting part?

I do need help with couting up unique staff inside a column.

Can you tell me about "DCount Aggregate functionality".

Thanks again
Greg

[Jim Doherty]

Your goal was laid out explicitly in your original question and an answer was formulated based on the specifics. If you had laid out the pecularities of renaming those fields originally then the answer would have been completely different. One can only make an assessment based on information provided as I am sure you appreciate?

'Domain Aggregate Functions' are fully explained in Access help and comprise DCount, DSum, DAvg, DLookup, DFirst, DLast, DMin, DMax amongst others. They allow you to aggregate on columns of data in a table or query based on criteria you specify

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

That is one way

Another way of counting/summming/looking up data is to design and write your own custom function that examines recordsets in code based on some parameter value(s) passed to your custom function. The result is returned to your calling procedure, or in the case of an query as a virtual column of data. Without seeing the specifics or what you are doing one can only guess here as to your design.

Yes... data can be thrown all over the place in code, but it has to follow a fixed logic pretty much like anything else. My only concern was is this worth all of the effort to keep this in Access ( nobody knows your big picture only you).

The original code I posted 'could' be dynamically rebuilt on the fly to take account of changing field names where the changed day of the week field name is passed into a function and then the whole lot applied as the .SQL property of some QueryDef object (query) in your database but obviously the required skill level is needed to do this as it is rather advanced.

Is the original design concept the best it can be? if not why not? Is the data being stored in such a way as to be sympathetic to the conventional use of queries. Do you want to be building extensive sql or code routines or otherwise building and developing based on an unorthodox foundation that may have to be unpicked later on.

I apologise if this is not helping you to a solution and of course feel free to re-post

[Jim Doherty]

As an addition to my last post and what you said about renaming fields. I have taken the time out here to try and illustrate what it is I am talking about and the code base involved in renaming fields, creating new tables the fly and restructuring in the manner that you seem to be working.

The attached zipped database hopefully will give you an insight into at least the code base logic in trying to return an SQL aggregated dataset in line with my first contribution but this time taking on board where you are inclined to change the table field names due to some suffix that you are applying. Whether it fits your needs remains to be seen.