Need help with query structure for a monthly report

[DJ726]

Greetings,

As I mentioned in my introduction post, I’m *very* new to Access, and am basically trying to learn it (when I have free time) because I’ve been told it will help automate and reduce the amount of work and time I spend building monthly reports in Excel.

Because I’m so new and the tutorials I’m reading seem to leave me with more questions than answers, I’m not even sure how to correct phrase my questions, so my apologies for the lengthy description that follows…

One of the first tasks I’m working on is creating a report that shows how many orders were entered in the system each month and how many orders were completed each month. On top of that, I need to sum how many circuits were associated with the orders, and then break it down into a running tally with the Months being the column headers, and an additional column for a quarterly sub total, as well as a Year to Date column, and the rows would be:

Count of new orders entered
Sum of circuits entered (each orders can have 1 – 8 circuits associated with it)
Count of orders completed
Sum of circuits completed

I’ve had some success with the queries and reports I’ve built, but because the resulting query seems to limit the report creation, I wonder if the source data needs to look different. I’ve also been told I can accomplish what I need with a crosstab querry, but when I try to build one I get error message saying something along the lines of not having enough columns. So I wonder part of the problem may be with the table itself.

All of the information I need for this report and query is contained in 4 columns of a multi column table.

Order type (New, Change, Disconnect)
Order Create Date
Order Complete Date
Quantity of Circuits

I tried building a query with multiple “groups” of columns, ie… the 1st column had criteria for the month of Jan, the 2nd column was the count of orders, and the 3rd colum was the sum of circuits. This worked fine until I added a 4th column which had the criteria set for the month of Feb, and the subsequent columns for the orders and circuits, which didn’t work at all… (this is where I was told a crosstab query would help)

And then to complicate this a bit further, the query will have two distinct catagories, one for new order types and one for disconnect orders.

(Also, there is no direct correlation between an order created and an order completed in any given month, and order entered in January and completed in March has no impact on any orders entered in March)

So I’m wondering how I need to approach this task? Am I able to create the reports I need based on the table & query I’ve created as described above? Do I need to structure something differently?

I’ve been able to create a basic report based on the first query I built from the wizard, but it has the months listed as rows and the order & circuit counts / sums as the headers, so that’s probably a question for the report forum…

Thanks,
David…

 

[plog]

Your table structure looks good. Can you provide some sample data from your table and then what you want the report to look like based on that sample data?

In my work we usually extract data into excel then pivot on it--its usually a lot easier than cross-tab queries.

 

[CJ_London]

All these things are possible

These are some basics - you'll need to substitute for your field and table names

[COLOR=seagreen]' To count new orders  this will prompt the user for year and month[/COLOR]
SELECT Count(1) FROM YourTbl WHERE Ordertype='New' and Month(OrderCreateDate)=[Enter Month 1,2,3...] and Year(OrderCreateDate)=[Enter Year 2010,2011,2012....]

Appreciate this does not answer all of your questions but should get you started.


As a newbie to Access, some recommendations

• Avoid spaces and odd characters in table, query and field names
• Avoid reserved words (common are date, field, table, name, type) - here is a link to the full list - http://support.microsoft.com/kb/286335
• Avoid using lookup in table field definitions

 

[DJ726]

Thanks Plog!

I'm not sure I can add files yet as I have less than 10 posts here, but I'll see what I can do though.

I've been using pivot tables in Excel a lot as well, and that's basically what's driving me to Access. Some of the other reports I have to build are much more complex and involves reviewing the progress of various work groups, how long the order has been in that particular groups workflow "bucket" and if the activity falls within a 30 day, a 90 day, or a greated than 90 day time period, etc...

The report structure I've been delivering has been changing, so there's an opportunity to reduce some of the effort in building it, but I at least want to see if Access can help with some automation of the tasks.

Hopefully I can post some samples here shortly!

Thanks again,
David...

 

[DJ726]

Plog, attached is a zip file with a sample Spreadsheet which contains the strucure of what I'm working with, and the results I'm trying to achive. (I went back & re-read the post about the 10 post limitation)

David...

 

[DJ726]

CJ_London,

Thanks for the link and recomendations on naming restrictions!

I'm not exactly sure how to apply the code, it looks like that belongs in a form as it's a prompt for a user?

David...

 

[plog]

What date field am I to use for the report?

How do I know if a record falls under 'New Circuits' or 'New Orders'?

You have multiple years worth of data in there, does all January get lumped together regardless of year?

When I finally do produce something, will it exactly match the numbers on the 'Report Structure' tab? Essentially is the report structure based on that data you provided?

 

[DJ726]

Plog,

Both the Order Create date and the Order Complete date are used, but in different scenarios. In the Report Structure Tab, New Orders Keyed (Row 4) or Disconnects Keyed (Row 7) shown will use the Order Create Date (Col C) of the Order Detail Tab. New Orders Completed (Row 5) and Disconnects completed (Row 8) will use the Order Completed Date (Col D)

Generally speaking, new (and change) orders are seldom completed in the same month it was created. So when an order is created and completed in the same month, the count of orders and sum of circuits would be reported twice. Essentially, each order is being reported on twice, once when it’s created and once when it’s completed, it's just generally not reported on in the same month.

Also, Column A in the Order Detail tab identifies the Order Type, (New, Change, Cancel or Disconnect). As a general rule, change orders are always counted as a new order and anything cancelled is ignored.

The record is associated with both New Orders and New Circuits as an order can have multiple circuits associated with it (unfortunately our data is a little "dirty" and there isn’t always a circuit qty associated with each order).

Sorry about the multiple years of data.... I’m just looking at 2013 data for now. When I created a very basic query, I used “Between #1/1/2013# And #1/31/2013#” as the criteria.

The quantites will match up as long as the filters / criteria is set for 2013.

For example, when the filters in the spreadsheet are set as follows;
Column A, Order Type: Change & New Install
Column C, Order Create Date: Jan 2013

The count of Order name (B) is 76 and the sum of New Circuits (E) is 338.

Then, when I clear the filter from Order create Date, and set Order Complete Date (D) to Jan 2013, the count of Order name (B) is 151 and the sum of New Circuits (E) is 710.

Hopefully this helps!
David...

 

[plog]

Good explanation. This is going to be hard to cram into the format you want, it would be a lot easier if instead of listing months horizontally you had them vertically:

. . . . . . . NewOrdersKeyed NewOrdersCompleted CircuitsOrderd CircuitsCompleted
January
February
March
...

In either case this is going to require some sub-queries and then a UNION. You have 4 sections to that report (row 4,5,16 &17), so you need 4 sub queries to get that data together. Here's 2, the other 2 you should be able to derive from these queries:

This query gets the new orders completed by Year, Month and Quarter. I call it 'sub_NewOrdersCompleted':

SELECT "New Orders Completed" AS ReportSection, Year([ORDER_COMPLETE_DATE]) AS Y, DatePart("q",[ORDER_COMPLETE_DATE]) AS Q, Month([ORDER_COMPLETE_DATE]) AS M, Sum(YourTableNameHere.NUMBER_OF_NEW_Cicuits) AS [Value]
FROM YourTableNameHere
WHERE (((YourTableNameHere.ORDER_TYPE)="Change" Or (YourTableNameHere.ORDER_TYPE)="New Install"))
GROUP BY "New Orders Completed", Year([ORDER_COMPLETE_DATE]), DatePart("q",[ORDER_COMPLETE_DATE]), Month([ORDER_COMPLETE_DATE]);

This query gets the new orders keyed by Year, Month and Quarter. I call it 'sub_NewOrdersKeyed':

SELECT "New Orders Keyed" AS ReportSection, Year([ORDER_CREATE_DATE]) AS Y, DatePart("q",[ORDER_CREATE_DATE]) AS Q, Month([ORDER_CREATE_DATE]) AS M, Count(YourTableNameHere.NUMBER_OF_NEW_Cicuits) AS [Value]
FROM YourTableNameHere
WHERE (((YourTableNameHere.ORDER_TYPE)="Change" Or (YourTableNameHere.ORDER_TYPE)="New Install"))
GROUP BY "New Orders Keyed", Year([ORDER_CREATE_DATE]), DatePart("q",[ORDER_CREATE_DATE]), Month([ORDER_CREATE_DATE]);

Then you will need a UNION query (http://www.techonthenet.com/sql/union.php) to bring all 4 (my 2 plus your 2 together) sub queries together. This is what the SQL looks like with my 2 queries in it:

SELECT *
FROM sub_NewOrdersCompleted
UNION ALL
SELECT *
FROM sub_NewOrdersKeyed;

Once we have that working we can make another query based on it where you can apply date criteria and either make a cross-tab out of, set it up to export to excel and do a cross-tab there, or create an Access report. Let me know when you have all 4 sub-queries working in that UNION query.

 

[DJ726]

Thanks Plog!

This looks like it's all built in the SQL view, correct?

Looks like I need to find an additional 24 hours somewhere, somehow this weeked! or maybe it'll be easier than I'm imagining! And thanks fot the link.

Hopefully will have some results to work with sooner than later.

David...

 

[plog]

Yes, you would paste that SQL into SQL view. Once there the sub queries should be able to be viewed in design view (posting SQL is just easier on the forum than screenshots). The UNION query however will not open in Design view--those just don't in Access.

 

[DJ726]

Hi Plog,

Sorry for the delay in answering... There was a slight report structure change I had to deal with 1st thing Monday morning so I couldn't give this project the attention I wanted to. But along the way I did learn that 1) table names with spaces need to have brackets [] arund them in a query and 2) spell check is important

So I've been playing with your query, including making some modifications from the design view, but I'm really not making much progress. Basically,

When I run the querry, a table is created, but without the content I was expecting.

I've attached a screen shot of the results, as well well as a screen shot of the results of the querry I built. Heres' the SQL of my query

SELECT [Trunk Order Table].ORDER_CREATE_DATE, Count([Trunk Order Table].ORDER_CREATE_DATE) AS CountOfORDER_CREATE_DATE, Sum([Trunk Order Table].NO_OF_NEW_T1S) AS SumOfNO_OF_NEW_T1S
FROM [Trunk Order Table]
GROUP BY [Trunk Order Table].ORDER_CREATE_DATE, [Trunk Order Table].ORDER_TYPE
HAVING ((([Trunk Order Table].ORDER_CREATE_DATE) Between #1/1/2013# And #1/31/2013#) AND (([Trunk Order Table].ORDER_TYPE)="Change" Or ([Trunk Order Table].ORDER_TYPE)="New Install"));

Thanks!
David

 

[plog]

You've lost me. How's this related to your original post? What are you trying to do? What results do you want? Again, lost.

 

[DJ726]

Morning Plog,

Sorry about the confusion. Ultimately I'm trying to get to a point where I can create a report that looks similar to the Report Structure tab in the DB Query and Report Example spreadsheet, which is basically a count of orders and a sum of circuits (T1's), per month, and ultimately, broken out by new orders and disconnect orders.

The goal was to have months as the column headers, with the counts & sums in the rows.

My initial thoughts were that I could create a query with multiple columns, and that I could have a table similar in structure to what the report would look like, but that didn't work out like I had hoped, which is what brought me here.

It looked like we were on track as of of your response on Friday, but when I ran the query you provided, the output wasn't what I expected. Also, I was prompted for an input when I ran the query, and if I leave it blank I get the results as seen in the screen shot I provided, and if I enter a value of 1, the value column has values entered, but don't match up with what's in the spreadheet.

So I'm not sure why this dialouge box is opening up, and what value I'm supposed to enter?

BTW, I only provided what I was doing as an example. I probably shouldn't have done that...

Thanks,
David

 

[plog]

That box appears because you don't have a [NUMBER_OF_NEW_Circuits] field in your [Trunk Order Table] table. My guess is its trying to order your data by that field. In design view of that query order your query by [ORDER_CREATE_DATE] then opening it. I think that will resolve that.

 

[DJ726]

Ok, I think I found the problem(s). First, the report structure I mentioned earlier.. I should have been more clear and described it as the Excel spreadsheet that my database tables are linked to. So.... the NUMBER_OF_NEW_Circuits field is what had changed to NO_OF_NEW_T1S. I made the change in the SQL, but apparently I also needed to change it in the design view field as well.

Once that was completed the query looked a lot better, but still wasn't populated with the values I was expecting, so after digging around, I discovered that some of the numeric fields in the spreadsheet were formatted as text, or general, and not as a number. Once I resolved that the values were very close.

The next item I realized, was that I have another column of data that I need to pull into the query, and exclude certain criteria. So far I have that working as well, but it seems that some of the counts and sums are still off by just a little.

So my next step is to start over from scratch and hopefully everything adds up properly, after that I think I'll be ready for the union.

Thanks!