UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help With Table Summaries If Possible :), Access 2007    
 
   
Eyribalt
post Jan 12 2018, 09:04 AM
Post#1



Posts: 4
Joined: 12-January 18



Hi there folks!

Would appreciate your advice on something I feel should be easier than I am making it... basically I have a number of tables with many fields, and I want to summarise the data population of said tables.

For example, suppose I have a table as follows:

ID | Field1 | Field2
01 | Bob | Fleming
02 | Sally | Field
03 | Jack |
04 | |

What I want is something that summarises what's filled in and what's not, with a percentage complete, so for the above:

ID | Field1 | Field2
Complete 4 3 2
Null 0 1 2
%Complete 100 75 50

Sorry for the dreadful formatting!

Any wise suggestions? At the moment I've been dumping totals queries into Excel, but I have 45 tables to summarise and it makes it very long winded.

Thanks in advance.
Go to the top of the page
 
GroverParkGeorge
post Jan 12 2018, 09:17 AM
Post#2


UA Admin
Posts: 31,561
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

First, I have found, over many years of fielding questions here at UtterAccess, that aliasing table and field names tends to be less helpful than actually explaining the content and context involved. For example, it looks like you have "FirstName" and "LastName" fields. Why not just say, "FirstName" and "LastName"? In other words, it might be helpful, therefore NOT to try to "simplify" things with generic aliases that hide as much as they reveal.

Next, you have 45 tables to summarize? Are they all based on this same set of fields?, I.e. Do you have 45 tables with "FirstName" and "LastName" fields? If so, that raises a big red flag about table design.

So, to your question,

You need to calculate the percentage of fields with values in them. The "example" you show seems not to reflect that, but lets say we can assume that what you want to say that in this set of four records, "Field1" is 75% filled in, and "Field2" is 50% filled in. In other words, there is a FirstName in three of the four records, and a LastName in two of the four records. Another way to look at it, though, would be to say that both records 1 and 2 are 100% complete (both FIrstName and LastName are filled in), record 2 is 50% filled in, and record 4 is 0% filled in.

So, let's step back, get a more realistic look at the real values being stored. Let's also understand why there would be 45 different tables with exactly the same fields? And how you actually want to calculate Percent Complete: by field within the whole table, or by field within each record.

Thanks.

--------------------
Go to the top of the page
 
Eyribalt
post Jan 12 2018, 09:42 AM
Post#3



Posts: 4
Joined: 12-January 18



Thanks George, all good points and I will endeavour to respond later, I'm about to clock out for the day. Quick game of badminton then back home for more grind!

Michael
Go to the top of the page
 
RJD
post Jan 12 2018, 11:36 AM
Post#4


UtterAccess VIP
Posts: 7,969
Joined: 25-October 10
From: Gulf South USA


H Michael - and Welcome to UA!: I agree with George. Using aliases here can often confuse a solution, and 45 tables - yes, some explanation on this. But I thought I'd just quickly create a demo to show you how you could go about getting the results you want, given the data you posted ...

CODE
SELECT "Complete" As Summary,Count([ID]) AS IDData, Count([Field1]) AS Field1Data, Count([Field2]) AS Field2Data
FROM tblMyTable
UNION ALL
SELECT "Null",Sum(IIf(IsNull([ID]),1,0)), Sum(IIf(IsNull([Field1]),1,0)), Sum(IIf(IsNull([Field2]),1,0))
FROM tblMyTable
UNION ALL
SELECT "% Complete",Count([ID])/Count(*), Count([Field1])/Count(*), Count([Field2])/Count(*)
FROM tblMyTable;

A functioning demo attached...

I used the UNION query approach to "stack" the results rather than show them all on one line/record. You may want to include all fields in one query (not UNION, one output record with all data), depending on how you intend to report this.

HTH
Joe
Attached File(s)
Attached File  TableSummaries.zip ( 17.96K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Eyribalt
post Today, 04:37 AM
Post#5



Posts: 4
Joined: 12-January 18



Hi, morning smile.gif

Ok, so a bit more detail then. I contract a railway manufacturer and as part of my remit, I have to populate 45 spreadsheets with data extracted from a database. They send me the spreadsheets weekly, and they contain site asset data, across 45 sites. Quite a few of the columns across the spreadsheets are the same, but sadly many are bespoke to specific sheets.

I have VBA a routine that imports all the spreadsheet data into 45 different tables, I then run update queries on the data, and repopulate the spreadsheets (this may be a further beg for help, as I am struggling to script something that successfully dumps my access data back into spreadsheets).

So... as part of the monthly client meeting, they ask for a summary on data population. This is basically a 3 line summary per spreadsheet, showing each column name, count of data populated, count of data not populated, and overall % populated, where % populated refers to the entire column (or whole field within table).

I appreciate the responses so far, I will take a look at the sample now. smile.gif

Michael
Go to the top of the page
 
Jeff B.
post Today, 08:24 AM
Post#6


UtterAccess VIP
Posts: 9,910
Joined: 30-April 10
From: Pacific NorthWet


So your "45 tables" represent 45 sites? There's no need (and some really good reasons not) to make Access tables look like spreadsheets. What about loading data into a SINGLE table, with one additional field to hold [site]?

You can use queries to separate out the 45 different sites, and/or reports, and you can export THOSE back to spreadsheets.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Eyribalt
post Today, 09:02 AM
Post#7



Posts: 4
Joined: 12-January 18



Yes, they represent the sites. As I mentioned above, there are many columns in these sheets that are specific to them only. If I were to draw the information into one table only (wish I could tbh), it would contain over 280 fields.
Go to the top of the page
 
GroverParkGeorge
post Today, 09:05 AM
Post#8


UA Admin
Posts: 31,561
Joined: 20-June 02
From: Newcastle, WA


At this point, I STRONGLY suggest you stop heading down this path, STRONGLY.

Invest some time studying the concepts of Normalization, which is the practice of designing relational databases. Your approach is based on the concept of spreadsheets, and that's not appropriate to Relational Databases, like Access.

We have some great articles to help you get started.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th January 2018 - 12:45 PM