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: 5
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: 33,766
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: 5
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: 8,925
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: 2
 
Go to the top of the page
 
Eyribalt
post Jan 16 2018, 04:37 AM
Post#5



Posts: 5
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 Jan 16 2018, 08:24 AM
Post#6


UtterAccess VIP
Posts: 10,140
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.
Go to the top of the page
 
Eyribalt
post Jan 16 2018, 09:02 AM
Post#7



Posts: 5
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 Jan 16 2018, 09:05 AM
Post#8


UA Admin
Posts: 33,766
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
 
John Vinson
post Jan 16 2018, 02:26 PM
Post#9


UtterAccess VIP
Posts: 4,270
Joined: 6-January 07
From: Parma, Idaho, US


I suspect that this may be an opportunity for the somewhat obscure use of One to One relationships for "Subclassing". I gather that your 45 sheets have quite a number of fields in common, but most or all of them have additional fields which apply only to that site - correct?

If so, consider having a structure with one "master" table containing the shared fields, with data for all of the sites; this would be related one-to-one with other tables, possibly 45 other tables(!) containing the site specific data.

You could then link to (not import) the spreadsheets you receive monthly, and run Append (not update!) queries to migrate the new data into your tables.
Go to the top of the page
 
Eyribalt
post Jan 17 2018, 08:20 AM
Post#10



Posts: 5
Joined: 12-January 18



Whilst I appreciate all input and ideas I feel my original question has been forgotten in favour of teaching me the basics of relational databases. On and off I've been an Access dev since it was born back in '92. I do understand relational databases and methods to achieve simple/quick queries and avoid data duplication. I am no expert by any means but I have a perfectly good import system written in VBA, that brings the data in, populates where it matches, then exports it back in a similar format. All in the click of a few buttons.

What I was wondering is if I missed something along my Access pathway (which is highly possible since I've trained myself) which made summarising table data easier. I have some ideas (thanks to Joe for his little DB sample).

smile.gif
Go to the top of the page
 
GroverParkGeorge
post Jan 17 2018, 08:24 AM
Post#11


UA Admin
Posts: 33,766
Joined: 20-June 02
From: Newcastle, WA


I strongly suspect that reluctance to address the underlying problem of non-normalized tables is leading to the rejection of suggestions on how to address that problem, which can, in turn, help address the original problem, which is that this non-normalized design contributes to difficulties in creating summary queries. Either create 45 summary queries, or address the normalization problem.

Go to the top of the page
 
Jeff B.
post Jan 17 2018, 08:53 AM
Post#12


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


What George said ... based on your description, your database would benefit from further normalization. Access features/functions work best with well-normalized data, not 'sheet data.

If you're saying "just tell me what button to push", good luck! Folks here probably assumed, as I did, that you were asking "how can I?" questions.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 11:03 AM