Connection to Union Query in Access (1 Viewer)

themurph2000

Fat, drunk, and stupid
Local time
Today, 12:32
Joined
Sep 24, 2007
Messages
181
I apologize for this one, but I inherited this for my new job last week.

The company I work for has a database of safety cards recorded in an Access database. They use pivot tables in Excel with the data linked in from a union query in access. The reason why is because they were attempting to document all of the different checkboxes on the original form and assign them points (2 major categories, 35 checkboxes per).

This resulted in a record source from Access of almost 600,000 records. Naturally, I can't re-establish the link between the spreadsheet's pivot table and the union query in Access (and refreshing it kills the records in the pivot table).

If someone has any thoughts on this, feel free to ask me some questions. Basically, I'm looking for a good starting point. I'm working on a new method to count up the check boxes, but what I've come up with (putting a point value field for each one) may not be enough.

Lots of :banghead:going on.
 

Rx_

Nothing In Moderation
Local time
Today, 11:32
Joined
Oct 22, 2009
Messages
2,803
The union query in access, is this what creates 600,000 records?
Do you need the details in Excel or just the summary data?

Provide a step-by-step so we can throw some solutions to you.
e.g.
Union Query runs against (x) tables.
Results in (xxx,xxx) records
Summary of Count (of grouping) for record categories results in (xxx) records
User wants details on category of one of (xxx) records
Create new worksheet on-demand for the single category of (xxx) records group.

If you can describe the source, the process and the intended results, we can probably suggest some steps. Big data in a single step can sometimes be challanging.
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 12:32
Joined
Sep 24, 2007
Messages
181
Sorry, yeah. The union query does indeed create 600,000 records. From what I can tell, they originally made a union query out of all the possible permutations for each check box (for each item to check, there are four checkboxes, thus four combinations, plus an N/A) So, for every record in the original data table (16,000 records), there is a record for each of the 35 sets of check boxes. (hence the 600,000 records)

Here's what I did. For each record, I assigned a point value to whether a check box is on or off (1 if on, 0 if off). This added 35 fields to the table, granted, but then there's only the original 16,000 records. I then added other fields to add up the numbers in a variety of ways.

Here's the big problem right now. I made a query to put together only the total points, which works just fine in Access. However, linking to that query through Excel doesn't work; all of the calculated fields come up zero.
 

Rx_

Nothing In Moderation
Local time
Today, 11:32
Joined
Oct 22, 2009
Messages
2,803
I think you are on the right track.
If you manually had the next to last step do a Create Table - assuming it outputs what you need, then link Excel to the newly created table (or against a query against the newly created table) - does it provide the intended results?
While Excel and Access play well together, there can sometimes be some data type conversion issues. For example a Check Box vs True / Fasle field.

Sometimes, I find createing a temp table at the very least gives me a troubshooting reference point. Plus it allows a query against it to perform some last second transformation that Excel can digest.

Doing these steps manually is just to see if it provides the intended results. If it does, then a subroutine can automate the process.
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 12:32
Joined
Sep 24, 2007
Messages
181
Good suggestion on the MakeTable idea. Access deletes out the old one every time, which should get me close enough until I have time to trash everything out and rebuild this thing. (The IT person here isn't thrilled with this database either and left me on my own.)

Thanks!

ADDITIONAL: This was a tough one because they handed me a database that was supposed to emulate a survey sheet containing a lot of checkboxes. Fortunately, with the Make-Table Query, I can then use the link to Excel (along with some trickeration) to make a count of items that they didn't have in a viable format. But I think I got it working now, thanks to this forum and a couple of others I did research on. :D
 
Last edited:

Users who are viewing this thread

Top Bottom