Report with Multi Query Data Source

[ckirmser]

I was using Excel to maintain a db of PCs that my company had converted to VoIP. We have a multi-floored building where each floor is separated into separate areas. The report for that building was a worksheet that had a cell assigned to each area.

To get the count for each area, I did a COUNTIF on the main db sheet to sum up those converted PCs in an area, using the area ID as the criteria to determine the count.

This worked fine in Excel. When I added a system in that building to the db, the chart showing the progress of that building would get the relevant area's count bumped.

Then, I decided that this db had grown to the point where it needed a real db tool to handle it and I imported the main sheet to a new Access db. That worked fine. I built the reports and queries that I needed and they all worked, except for the one that updates the building mentioned above.

When I make the query to count up the relevat PCs in each area, it counts OK, but each area gets its own record. That seemed fine at first, but I can not see any way to make a report that can select specific records for certain fields in the report body.

Then, I tried making a separate query for each area. That worked fine, but I can't have a report based on more than one query (that I can see).

So, I says to Access, I says, "OK, smart guy. Think yer gonna beat me, eh? Well, I'll show ya!"

With that, I made a new query and added each of the separate area queries to the new one, thinking I could just grab the sub-query result as a field in the new query. I figured that I'd be able to just grab the field to assign to the relevant field in the report. Well, Access decided to be persnickity and refused to do what I wanted.

Thinking I'd look silly slapping my computer around, I decided to join this forum and ask...


HAAALLPP!!!

 

[Len Boorman]

Have a Main Report that atually has nothing in it. Then drop you 4 reports (or however many it is) in to the detail area as subreports. Do not set ant Parent Child links

Open the Main report and the other reports will also be opened and yoiu have a 4 in 1

You will have to play with soem text to act as headers fo rindividual reports and maybe put in some page breaks but its okay really

len B

 

[ckirmser]

Thanx, Len. I thought of that, but it wouldn't look as nice as my Excel page. I've got 108 different areas to account for and that would be 108 sub-reports. I don't know if Access would belch or not with 108 different sub-reports, but I guess I can try it later on and find out.

If there were a way to assign specific records to a text field's data source, that'd work just fine.

I also thought of making the area queries into MakeTable queries, merge the resultant tables and then make a separate query to work on the big, merged table. But, when thinking about it, I figured that such a method would require all the sub-queries being run before it would update the tables that make up the big table that is the basis for the main query that fills the report that lives in the house that Jack built...

Oi! Maybe I should just return to Excel. After all, it worked there. But, dadgummit! Access is based on Excel, so I figure what Excel can do, Access should be able to do...

 

[Len Boorman]

Re Read your question and answer

Can you not use one report and then group on area ?.

How many tables are you using ?

Len B

 

[ckirmser]

Len -

I have one table that is, essentially, an inventory list of those PCs converted to VoIP. My employer has about 200 locations city-wide, with 108 of those within one, multi-storied building; 9 floors with each floor divided into 12 areas.

My Excel sheet handled it admirably by using the CountIf function off of the Location field, but that doesn't seem to translate to Access. All I had to do is assigne a cell in the report sheet to each location, and do a CountIf of that location to get the count.

Like I said above, if there were some way to assign a textbox in Access a particular record from a table, that'd fix it right there. I already have a query that counts for each location, but it gives each location it's own record. If I could do something like set the Control or textbox1 to Record1, textbox2 to Record2, textbox3 to Record3, etc., that'd be great. But, I can see no way of setting records to a field directly.

 

[Pat Hartman]

It sounds like you simply imported your spreadsheets into tables rather than normalizing them. You will find that Excel is a far better spreadsheet than Access and Access is a far better database than Excel. To take advantage of the performance gains of using a database, you will most likely need to redesign your application so that it works with normalized tables rather than psuedo spreadsheets.

You may find that DCount() solves your problem. Domain functions can be assigned directly as ControlSources. The DCount()'s criteria will allow you to control the domain of records to be counted.

 

[ckirmser]

Pat -

Normalized? Hmm, when I imported the spreadsheet, it did ask me if I wanted the baseline table broken up into a number of smaller tables to minimize errors for fields that have similar values. Is that what you mean?

The end result of those smaller tables being grouped together is that it made a query that duplicates my complete table and I use that to build everything else.

I'll check out DCount and see what that can do for me.

Thanx!

 

[Len Boorman]

The advice from Pat is as always spot on.
What I am now going to suggest is a get out of jail possibility only. Certainly you should consider normalising your application.

You have one table so if you have a query that selects
Location
Building
Floor
Area
pc detail

and then group with a Count on the pc detail the result could be the count of PC's within an Area/Floor/Building/Location

As I said its a get out of jail type fix only. If your Location, Building, Floor and Area entries are not consistant then you will get records that are not completely grouped. Normalisation would resolve that problem.

Maybe buys you a bit of time

Len B

 

[ckirmser]

Len -

Going by what you posted, my table is normalized. The main table is actually a query that pulls together data from a number of sub-tables that contain data that was originally all part of the same Excel sheet. So, the location data is consistent.

I think I already have a count query that does as you suggested for the "get-out-of-jail fix." Unfortunately, it assigns the count of systems for each location to a separate record in the query result. The data is there, but not in a form that matches my existing Excel report style. I may have to go with it, particulary if DCount doesn't answer the problem (but, I think it does). I just wanted to maintain a similar format so that management is not confused by seeing something they're not used to.

Thanx for your help!

 

[Len Boorman]

Okay. Understand your comment about main table.

Re the get out of jail

If you leave say Area out of the query then the Grouping will be

Location
Building
Floor

and the count of say PC will reflect this revised grouping

if this helps

Len B

 

[ckirmser]

Many thanx, guys! The DCount did it. Ah gots mah report!

'Course, it doesn't look quite as good as the Excel one (smaller selection of line styles), but the data is where it should be and displaying the right amounts.

Now, my boss will have to put away his guillotine for another time...