Creating Report From Multiple Sources in Access 2010

[JeffGeorge]

I am trying to create a columnar report that relies on information that are expressions in various queries. Unfortunately, I can't seem to create them in one query because the criteria is mutually exclusive. I used the report wizard to start the report based on the single expression in the first query. Now I want to start adding the other data but I can't seem to get it to work. All I really want to do is include fields that have the have the value, which is already summed, from the other query. When I try that I get error messages like "you tried to execute a query that does not include the specified expression 'expressionName' as part of an aggregate function' and it just goes further downhill from there. What is the easiest way to get this simple values into the report? Is there a way I can create a field in the report that uses a SQL statement for just that field so I don't have to have the report linking to 50 different queries?

[rpeare]

Any possibility of seeing a sample, I can't really understand what you're trying to say.

[JeffGeorge]

Sure; not a problem.

This is the SQL statement from the query that I used to create the report:

SELECT Sum([PERIOD_1]*-1) AS Revenue
FROM dbo_GLCHARD
HAVING (((dbo_GLCHARD.[GLACCOUNT])>="4010.01" And (dbo_GLCHARD.[GLACCOUNT])<="4100.02" And [FISCAL_YEAR]="2013")) OR (((dbo_GLCHARD.[GLACCOUNT])>="9625.00" And (dbo_GLCHARD.[GLACCOUNT])<="9626.02" And [FISCAL_YEAR]="2013"));

This is the first of many queries that provide single values to the report. Another one is:

SELECT Sum(dbo_GLCHARD.PERIOD_1) AS MatCOGS
FROM dbo_GLCHARD
HAVING (((dbo_GLCHARD.[GLACCOUNT])>="5010.01" And (dbo_GLCHARD.[GLACCOUNT])<="5100.02") AND ((dbo_GLCHARD.[FISCAL_YEAR])="2013"));

However, I can't seem to get the value that query returns into the report without problems. There will eventually be probably 20-30 different queries like this that will need to be included in the report. The only solution I've found so far is to build a master query which calls all the sub-queries and have the report call the master query. I'd like to be able to cut out this extra step and just do it all in the report. I was hoping there was a way in report design that I could just add a field and put some SQL specific to that field. That way I could just write the queries as I add the fields.

[rpeare]

How do you intend to link the two sources? Your first query has one field, that being Revenue. Your second query has only one field MatCOGS. You don't have anything to link the two queries. You likely have to include the PK of the dbo_GLCHARD table in both queries so you can link them correctly then sum the data rather than summing it then trying to link to unrelated queries (unrelated simply because there's no way to connect them based on the data currently in them)

[JeffGeorge]

That's what I was afraid of. I'll try experimenting with adding a field to the queries that I can use to link them and sum them inside the report instead of the query. What happens, though, if I then need data from a completely unrelated table/query? How would I get that into my report if there is nothing I can use to link the two sources?

[rpeare]

I'd have to see an example of the database itself to tell you. if you are correctly establishing primary keys in all your tables and your data structure is normalized properly everything can be linked together though they may be 3 or 4 or 5 tables removed from one another.

At the very least if you show an example of your dbo_GLCHARD table with what the data currently looks like and what you WANT it to look like that would be easier then something completely theoretical.

[JeffGeorge]

The database itself is rather large. It's an MS-SQL database that is used for industry-specific enterprise software. Unfortunately, the built-in reporting aspects are not user-friendly or detailed, so I'm seeing what I can come up with by using Access to build some reports. The current attempt is to create a simple P&L statement, but I have to pull data direct from fields in the G/L detail. The key fields are the Fiscal Year, the G/L Account, and the Period#. For example, just to get the sales value for the first month of 2013 I have to make sure Fiscal Year = 2013, Period = Period 1, and then add up a specified number of accounts from the G/L Account field. Then I have to do the same for expenses, etc. The catch is that I want to be able to make this report accessible as a desktop shortcut to various users but without having it open the actual database the report is designed in or prompt for the SQL credentials. I also want to be able to prompt them for which month they want to see the statement for, but I'm not quite sure how to do that and then to convert that input to something usable. For example, if they were to input May 2013 I'd have to have some way of converting that to take account values from PERIOD_5 and have FISCAL_YEAR="2013". Maybe it's time to just buy Crystal Reports....

[rpeare]

If you're using access you'll have to open a database, there's no way around that that I know of. However, since your users are not using it for data entry it shouldn't matter how many people you have in your database at any given time which makes it easier to design because you don't have to plan around minimizing the impact of multiple concurrent users. I'm not asking for your original data, I'm asking for a sample of your data, fake data, but without seeing exactly what you're trying to do in terms of your data it's hard to give you direction. I don't know about other reporting mechanisms but crystal would definitely allow you to have a shortcut on a desktop that just opens the report you're talking about, but putting it in access would allow you to have one source for reports rather than having to have multiple shortcuts.

[JeffGeorge]

I'm trying to make it as simple as possible for these users. I don't think the people in charge would really want them to have the SQL sa account credentials so it would have to be some sort of passthrough where they wouldn't have to enter anything. Even then I know right now that if they click on a shortcut and Access opens up and they see all kinds of queries and reports and ODBC database connections listed that they will be like deer in headlights, not even thinking of the fact that there's no way I want, nor would I get approval for, these people have live access to the database tables.

Here's an example of a record from the table I'm starting with for the first report (I just grabbed one at random so the data may be garbage). This is just for one account for one year. Not only would I have to compile all the records from various accounts that meet the user's period and fiscal year combo but then I also have to pull the corresponding data for year to date information and for the the same period of the prior year and the prior year year to date. I reiterate that the userbase is not computer and application literate. They need to be able to click on something, put in the criteria, and see the results. I'm now starting to think that I might need to build some kind of application that be run periodically to update an intermediary table where all the sales, expenses, etc. are stored so that the user report doesn't have to jump through all these hoops to get what should be easily accessible data. I'm used to working with ERP software where I can just pick a field that already has YTD sales and stuff like that. This source software isn't as sophisticated. It doesn't even offer the ability to design your own reports. And I'm not crazy about the idea of using something like Crystal Reports because then they would have to purchase it and I really have no experience working with it.

PERIOD_1

DIVISION

GLACCOUNT

FISCAL_YEAR

TRAN_TYPE

BEG_BAL

PERIOD_2

PERIOD_3

PERIOD_4

PERIOD_5

PERIOD_6

PERIOD_7

PERIOD_8

PERIOD_9

PERIOD_10

PERIOD_11

PERIOD_12

PERIOD_13

PERIOD_A

CUR_BAL

CREATEDATE

CREATETIME

CREATEUSER

CREATESTATION

LASTDATE

LASTTIME

LASTUSER

LASTSTATION

-1286

1

1000.00

2006

A

5866.6

10122.69

-10203.29

378.36

482.14

-608.7

2059.02

-978.8

586.8

-178.41

-23.73

-1716.68

0

0

4500

03-Jan-06

03-Jan-06

LW

CASHIER

16-Jan-07

16-Jan-07

KR

KRAMIN

[rpeare]

For your first paragraph. If your users are not Access users you can hide all database objects and set it so when your database opens all they see is a switchboard with perhaps some data entry fields so they can choose a customer or date period etc. They would not have to see the actual SQL tables. Additionally, when you link a SQL table, if you link it without defining a PK (primary key) the table is not editable so they can't screw up the data even if they figure out how to show the tables.

So, if I understand correctly. if this record indicates one customer for one year, the periods indicate the months of the year, and you may have mutliple years for this customer and you want to, for instance, pick a customer pick a year, pick a period (say 3 years) and have the report show you the year you enter and the 3 years prior to that year for comparison? or are you comparing specific periods for instance are you wanting to show a graph of each period separately or in summary (all periods added together graphed over the period defined by the user)

[JeffGeorge]

For your first paragraph. If your users are not Access users you can hide all database objects and set it so when your database opens all they see is a switchboard with perhaps some data entry fields so they can choose a customer or date period etc. They would not have to see the actual SQL tables. Additionally, when you link a SQL table, if you link it without defining a PK (primary key) the table is not editable so they can't screw up the data even if they figure out how to show the tables.

This sounds like the kind of idea I want to implement. Do you know of any good online references for setting up this kind of configuration for an end user? I would still have to somehow pass the SQL credentials somewhere, though, right?

So, if I understand correctly. if this record indicates one customer for one year, the periods indicate the months of the year, and you may have mutliple years for this customer and you want to, for instance, pick a customer pick a year, pick a period (say 3 years) and have the report show you the year you enter and the 3 years prior to that year for comparison? or are you comparing specific periods for instance are you wanting to show a graph of each period separately or in summary (all periods added together graphed over the period defined by the user)

Actually, this sample record is from a General Ledger table. It shows how much that G/L accrued for each period of that particular FISCAL_YEAR. Some accounts represent revenues, some represent expenses, etc. The report is basically a columnar report showing revenues, expenses, and a number of calculations. In its simplest form it would have a minimum of 4 columns, but it could also have columns for calculated variances between year and the next. Here's an example:

Suppose the user wants the Profit & Loss statement for March 2013. The immediate filter for this would be to relate their input of "March" to PERIOD_3 and the FISCAL_YEAR would be filtered to "2013". Now, there could be as many as 20+ queries behind this. One query would sum all the PERIOD_3 values for "2013" in GLACCOUNT numbers that are defined in the query to revenues. Another query would do the same for expense accounts, overhead accounts, etc. until you have a plethora of values that will appear on the report. Now next to these values it would show the same thing except for FISCAL_YEAR="2012". Column 3 would be YTD 2013 so it would include PERIOD_1, PERIOD_2, and PERIOD_3 where FISCAL_YEAR="2013". The next column would be that same information but FISCAL_YEAR would equal "2012" for the 3 periods. All of this has to come from that initial user input of March 2013 (or however it would be done).

It would be great if I could just add a field to the Access report and add some code behind it to say something like

SELECT Sum([PERIOD_1]*-1) AS grossSales
FROM dbo_GLCHARD
HAVING (((dbo_GLCHARD.GLACCOUNT)>="4010.01" And (dbo_GLCHARD.GLACCOUNT)<="4050.04") AND ((dbo_GLCHARD.FISCAL_YEAR)="2013"));

Unfortunately, I cannot do this, so I'm working on creating a master query that has all the other queries (such as that code above) and calculated fields and then just populate the report with those. Of course, I'm still not sure how to secure the output or take user input, but there has to be a better and easier way of doing it. Crystal Reports allows you to create a field based on a SQL expression but does not allow SELECT statements that return more than one value, so I really don't see the use of it at this point. I believe most users here have Access on their systems so that's another reason I'm trying to do it this way. If nothing else, this may ensure an extended visit at this company (even though I'm only doing 3 days a week for them at this point).

[JeffGeorge]

Wow. I wrote a lot. I need to work on that longwindedness.

[rpeare]

Ok here is my point.

You don't appear to need the 20 queries, I suspect you can do this with way fewer. Does your GL account table have an indicator of which GROUP the data falls into (expense accounts, overhead accounts, etc)

If it does you can link the GL accounts table to your summary table through the GL account number and perform a summary of the groups in a formula OR, worst case scenario, let's say your expense accounts are in the 1000 - 1999.9999 range you could have a formula in your query that says

ExpenseAccounts: Sum(iif(GLAccount >= "1000.00" and GLAccount <= 1999.999, [Period_3], 0))

In other words if your gl account is between 1000.00 and 1999.999 use the period_3 value, otherwise use zero, then sum those values.

I'm guessing that your the table(s) driving this GL summary query are normalized and that some background function is updating this table from time to time. I'm also guessing that if you want to show a certain period it would be far easier to use the actual data than this summary table in relation to the period_3. For instance your source table would likely have a specific date in it so it would be easier to pick out the month of the activity based on the actual date (for instance 2/15/2013) rather than converting a "MARCH" input to Period_3). However, if you are bound and determined to produce a report based on this GL summary table you would likely have to create the record source for the report on the fly in the ON OPEN event of the report and not rely on a static query.

So let's say you have two ranges of GLAccounts (just for the purposes of an example, let's assume anything that starts in the 1000 range is REVENUE, anything in the 2000 range is EXPENSE ACCOUNTS and let's say you want to see 3 years worth of data, starting with 2013 and going backward to 2011 do you want your final data to look like this:

GLAccount	Month	2013	2012	2011
REVENUE	March	5000	4000	6000
EXPENSE ACCOUNTS	March	2000	3000	4000

Where the totals would be the totals for march only for each of those given years (or potentially the sum of all activity through the end of march for each given year)

If you do, there are two ways to do this in a query, one is called a crosstab which you would never have to modify the design of, the other is to build a static query that you'd have to update as you add more fiscal year, I prefer the crosstab, but in order to build a report (a polished report rather than just the query itself) is going to require you to do some coding because you can tell that the number of columns may change (let's say you wanted to see 5 years of data rather than 3) and the column headers will also change (2 years from now you'll have 2015 and 2014 and 2012 and 2011 may drop off).

As far as your linking method. If you have control of the SQL server it's far easier (and faster in general) to create and ODBC connection for each workstation that uses NT authentication when a person connects to the SQL tables, however this would require the user to have a USERID on the SQL database that matches their WINDOWS login... for instance Bob Smith logs into the network using bsmith/goober, bob would also have to have SQL login of bsmith/goober in order to link without having to input a username/pw. If your users DO NOT have a login to the SQL database that matches their WINDOWS login exactly you would have to pass some variables to generate a link at run time of any of your code, but be aware if you do this you will have to have some VB script code and if someone knows their way around and you are using an administrator username/pw they will be able to see that. It's safer to have a generic SQL login that is read only permission and you connect to the SQL database using that generic ID in your VB script.

[JeffGeorge]

It appears from the proposal I've been given that they will ever only have to see two years max, the year chosen by the user and the previous, which makes things a little easier. In your example above the values would only include March; there would be a separate YTD column next to it to show the Jan-March values. Unfortunately, this table does not have a field that indicates what kind of account it is. I could link it to the G/L Header table which has such a field, but it only accepts a few values, and it uses the same value for Income and Expense.

I think that I definitely would want to add the users as read-only SQL users so that there would be no need to prompt for credentials. I have access to the SQL server but since I don't really work here I'm not going to create the accounts until someone oks it. They don't have a local DB admin or even a network admin, for that matter.

I've been poring through the table structures trying to determine where else it can store the values but so far I'm not having any luck finding anything. The software itself is pretty locked down from a technical end and the vendor is not overly forthcoming about giving out information for what goes on behind the scenes. One of their money-making activities is building custom reports for their customers, so you can understand why they don't want to give me too much information. In fact, there really aren't any troubleshooting or debugging tools on the client end, either. When I look at an app form there's no way to see how it's being populated or to determine the SQL statement being sent to the database when performing an action. Actually, considering that there's no actual client-end portion of the software I shouldn't expect to be able to do local debug logs and captures. Maybe it's time to start snooping around the SQL server to see what's going on over there!

And I appreciate your knowledge and input on this issue. Every thing you've said makes perfect sense and looks like the best way to approach things. Now if I could just find a way to make it simple....

[rpeare]

The solution to your GLAccount problem is to create your own within access, for instance let's say

1000.1
1000.3
1000.5

are revenue accounts

1000.2
1000.4
1000.5

are expense accounts

just create a local table with the label you want for the GL accounts if it is not the database defined one.

In your situation I would check to see what the current database users are, check to see if they are identical to the person's windows login. (you can just ask the people if they use the same username/pw for both logging into their workstations and the software)

If they DO NOT use the same username/PW combination for both, ask the powers that be to create SQL logins that match their workstation logins. Then you can create linked tables (which are far, far easier to deal with if you are a neophyte VB scripting programmer). You can create those SQL logins for the database so they are read only and individual to each users. It's a little more work to do things this way, but in terms of building your queries/reports it's far, far, far easier to deal with.

please, if we're going to continue this conversation post an example of what you want your FINISHED product to look like (assuming you're using the dataset you posted earlier), I'm tired of guessing