Complex Query (1 Viewer)

[Drand]

Hi All

This is a bit messy!

I have a table with 57 fields which denote responses to a survey.

There are 135 companies that provide the data, each with a number of staff who respond culminating in a table with over 250,000 records.

Not every company has to supply data for all of the fields. The required responses are specific to each country.

My goal is to locate fields that have not been completed (null), again specific to the rules for that company.

To that end, I have another table (tblDataRules) with the same fields as the raw data table, with yes/no responses for each company if data is required or not. The objective here is to find null values, and then check if that field is required for that company, and then do something if it is required data.

I have tried a query that includes the raw data 57 fields, and establishes the null values. I added the tblDataRules to the query, each field with a where expression set to true.

Because of the number of fields in the query this becomes too complex for Access to evaluate.

Is there another way I can approach this?

Would appreciate some guidance on this.

Many thanks

 

[ebs17]

Inappropriate table and data structures provoke difficult and complex processing.
With processing-ready structures, processing can be very easy and fast. Therefore, database development always starts with data modeling, the sports car SQL just runs faster and more relaxed on a slippery road than when you send it off-road.

I have a table with 57 fields which denote responses to a survey.

Don't use Yes/No fields to store preferences
I think you need an UNPIVOT as a first step. With a list table instead of a matrix table, queries become very easy.

If you should continue here with suggestions: for this you need to know tables and their structures, not just stories, there are ...

It would be practical if you provided the original table with records reduced to a few cases (data anonymized, but not alienated from the meaning) in a database.

 

[Drand]

Inappropriate table and data structures provoke difficult and complex processing.
With processing-ready structures, processing can be very easy and fast. Therefore, database development always starts with data modeling, the sports car SQL just runs faster and more relaxed on a slippery road than when you send it off-road.

Don't use Yes/No fields to store preferences
I think you need an UNPIVOT as a first step. With a list table instead of a matrix table, queries become very easy.

If you should continue here with suggestions: for this you need to know tables and their structures, not just stories, there are ...

It would be practical if you provided the original table with records reduced to a few cases (data anonymized, but not alienated from the meaning) in a database.

Thanks for the response.

Attached is a sample table of the raw data.

 

[ebs17]

The work is done without doing anything. There are no gaps or NULL content in your table, apart from the complete fields 2 and 3. You would see for yourself.

The thing shown is a complete waste of time. Please just waste your time.

 

[Drand]

Wow. You asked for a copy of the table. I assumed you wanted to see the table structure not the data, assuming that the real data would have Null records.

 

[arnelgp]

respond culminating in a table with over 250,000 records

the uploaded db is but a sample.

i was thinking maybe you will be needing a VBA (recordset) to match those "unanswered required fields".

 

[Drand]

Thanks

 

[arnelgp]

but without your "rule" table, we wouldn't know which field is required for a specific company/country.
also since there is no "blank" fields on the sample, you can provide a simple "case" study demo.

 

[Drand]

Thanks again

Hopefully, the attached will clarify!

The point with this is, any company may or may not be required to complete a particular field in the survey. tblDataRules is designed (rightly or wrongly) to look at this.

 

[Drand]

Thanks again

Hopefully, the attached will clarify!

The point with this is, any company may or may not be required to complete a particular field in the survey. tblDataRules is designed (rightly or wrongly) to look at this.

Sorry. Just assume that across 250,000 records there could be blanks in any field in any record

 

[arnelgp]

you run fnRunRule function in Module1.
it will save the id of any record that does not pass your table tblDataRules to tblDump.
view the result in query1.

 

[Drand]

Thanks again for this.

I have imported into my project but when the run the function I get an error "Item not found in this collection" at the line

varValue = .Fields(fldName).Value

 

[Isaac]

I was going to suggest a VBA/recordset solution for this (definitely without bragging, as there may be a much superior SQL-based solution), but it looks like it was already done. Does seem like a reasonable scenario for VBA.

Especially if this is only a one-time or occasional thing, and if there is not any particular benefit to having the results of the check totally integrated into the database itself, I would also consider Excel VBA for this (data dump to Excel and then using various Excel vba methods to check it).
Excel is extremely fast in checking its own cell data, sometimes I feel a bit faster than certain Access scenarios involving considerations regarding recordsets, cursor types and paging/seeking/screen rendering-type of activity.

Glad it sounds like you got something that works

 

[XPS35]

I have a table with 57 fields which denote responses to a survey.

IMHO your design is totally wrong. This is Excel not a relational database design. I think your database for a one time survey should look something like:

tblQuestionAppliesToCountry discribes which answers apply to a country. You can generate records to the tblAnswer initially based on the content of the other tables.

 

[arnelgp]

the function I get an error "Item not found in this collection" at the line

that is why in my code there is a code:

On Error Resume Next

you have FirstName and LastName field in tblDataRules table which are not in tblConsolRawData.
copy tblDataRules to new table (as backup) and remove those fields not in tblConsolRawData.

 

[Drand]

Sorry, my mistake.

There were actually a few anomalies in my tables which I have now fixed.

The function now works brilliantly.

Once again, thanks for your assistance and patience!

Cheers

 

[Drand]

Sorry arnelgp.

I have been doing some testing and one thing is puzzling. The function does not recognise missing data in the second column., "Survey Methodology (Legacy Anonymous / Linked Data)". It works for all other columns just not this one.

 

[arnelgp]

on your sample data, tblConsolRawData, you only have CountryCode 3 and 9.
on your rule table, tblDataRules, CountryCode =3, "Survey Methodology (Legacy Anonymous / Linked Data)" field is Not Requied.
while on CountryCode = 9, it is Required field.

therefore, only countrycode = 9 are imported to tblDump.

i did a test again on your sample data.

 

[Drand]

Sorry, obviously that was only a sample of the data. There are actually 124 country codes with rules applicable to each of these. When I turn the rule on/off it works for all fields except the second one.

 

[arnelgp]

try it with sample data.
copy your "raw" data to another table.
then from the "raw" data, delete all records except 5 or more countries with blanks on that fields.
run the function.
and compare the result with that of your "rule" table.