Solved

Append Data TO Excel FROM an access table

Posted on 2014-03-28
13
310 Views
Last Modified: 2014-04-06
Good Afternoon Guru's,

Here is the issue.  We have a Spreadsheet.Worksheet that needs to be updated based off of 3 separate access tables.

The structure of the worksheet is like this:

column1     column2   column3    column4   column5   column6(Jan)       column7(Feb)

Grp1             Value1                                                         celltoupdate       celltoupdate
Grp1             Value2                                                         celltoupdate        celltoupdate
Grp1             Value3                                                          celltoupdate        celltoupdate
Grp1             Value4                                                              etc...                 etc....
Grp1             Value5
Grp1             Value6
Grp1             Value7
Grp1              Value8
Grp1              Value9
Grp1              Value10
Grp2              Value1
Grp2              Value2
Grp2               ECT...
Grp2
Grp2
Grp2
Grp2
Grp2
Grp2
Grp2
Etc....

The structure of the access table is:
field1    field2   field3   field4     field5    field6   field(Jan)   field8(Feb)   field9(March)


Fields 7 thru 14 are named as each month of the year starting with January.

What we need to do is place the values of each month from the access database to the appropriate cell in the worksheet.
So, using the naming convention above the process would be something like this.

Update column6(Jan) column7(Feb)  etc.....
From access.table
where worksheet.column1 = access.table.field1 AND worksheet.column2 = value1

How would this be done?

Going beyond the connection issue, that is no problem.  The problem is how to dynamically put the values from the access table into the correct cell in the worksheet.


Thanks in advance !
0
Comment
Question by:UserName935
  • 8
  • 4
13 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39962455
Dynamically?  Do you mean some kind of a live link, or code to select the data in Access and then export it to the workbook?
0
 

Author Comment

by:UserName935
ID: 39962578
Yes, that is correct.
As values in the workbook.worksheet.column1 will "come and go".  For example, there may be a value of XXX in column one for the month of Jan, then in Feb the value of XXX would be 'gone' or 'decommisioned'.  Also, a value in  workbook.worksheet.column1 may NOT be there on Jan, then, in Feb a value of, say, f"oo" would be added.

Calculations are being completed on the access side in regards to Jan, Feb, Mar, etc... fields and these values from the DB need to placed ONLY in the column that matches,
for example:
The first column in the worksheet is being used as the data to match that of the first field in the DB.

 so,            DB.Field1 = worksheet.workbook.column1

DB.TableName.Field(Jan) would be placed in Workbook.worksheet.Grp1.Value1.Jan
and
DB.TableName.Field(Jan) would be placed in Workbook.worksheet.Grp2.Value1.Jan
and
DB.TableName.Field(Jan) would be placed in Workbook.worksheet.Grp3.Value1.Jan
and so on...

Does that help?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39966259
That looks like you could use MS Query to pull the data from Access and have it update each time. I suspect this would pull individual lines rather than Summary per line.

Or you may be able to use a Pivot Table with the Access table as the source data.

Thanks
Rob H
0
 

Author Comment

by:UserName935
ID: 39966628
Yes, that is correct, a pivot table is being used.  However, I am having a time mapping the data from the 3 DB data sources and "matching" that data to where, or which cell that data will go to on the excel side.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39966884
Does your pivot link directly to the database or are you capturing the data into an excel sheet from which you are creating the pivot?

If doing it directly, the data link for the pivot should align the columns correctly.

I know you can create a pivot from multiple sheets within the workbook but don't know if you can with multiple external database tables.

Thanks
Rob H
0
 

Author Comment

by:UserName935
ID: 39966905
The pivot table data is completed in the DB with a query.  I think the main issue is getting lost.  Let me refer back to the orginal question.
so, there is a worksheet that is structured like this:

column1     column2   column3    column4   column5   column6(Jan)       column7(Feb)

Grp1             Value1                                                         celltoupdate       celltoupdate
Grp1             Value2                                                         celltoupdate        celltoupdate
Grp1             Value3                                                          celltoupdate        celltoupdate
Grp1             Value4                                                              etc...                 etc....
Grp1             Value5
Grp1             Value6
Grp1             Value7
Grp1              Value8
Grp1              Value9
Grp1              Value10
Grp2              Value1
Grp2              Value2
Grp2               ECT...
Grp2
Grp2
Grp2

Problem:  I need to get query results from the DB to the excel sheet.  
Say "Value1" from each "Grp", being Grp1, Grp2 and so on,  is going to be an aggregate number that indicates "headcount".  In the DB these results are shown all in order.  Now say, the second query in the DB is going to fill "Value2" of Grp1, Grp2 and so on in the worksheet and this value denotes 'the total amount of money use for a period of time.  Now say, a third query in the DB is going to grab "projected budget" for "Value3" of Grp1, Grp2 and so on.
Does this help in explaining the issue.  We need to merge the data from the DB and put it into a certain order in the spreadsheet.

Does that help explain things a bit.  And by the way, I certainly appreciate your help and assistance, this one is kicking my bum.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Expert Comment

by:Rob Henson
ID: 39966929
So you are using a query within the database to combine the 3 tables and showing the required results in one query result table.

You could then use MS Query within Excel to pull the results of that query into Excel but that would probably give all lines of the query result so if the Pivot Table links directly to the query result it will summarise as required.

The Pivot table can be formatted such that it repeats row headers so it ends up looking like the table you are describing.

Are the Jan to Dec entries numeric values?

Thanks
Rob H
0
 

Author Comment

by:UserName935
ID: 39966982
Umm, that's getting closer,

 Q.      ......"So you are using a query within the database to combine the 3 tables and showing the required results in one query result table....."

 A. No, there are about 20 queries that get reduced to 3 mutually exclusive queries, then the result set from each of the 3 queries 'get's placed' into the pre-formated worksheet.

I am somewhat bright about access and about totally stupid with excel.

Question:  Would be possible to dump the results from each query into a separate worksheet within the workbook, then from within excel combine the separate outputs in that fashion?
0
 

Author Comment

by:UserName935
ID: 39967770
Come on experts, is this one that hard, or impossible?  Please let me know and I will throw in the towel.  There really is not to much time to miander about and my blood pressure is seathing.....

Anything I can do to help assist, please let me know.
0
 

Accepted Solution

by:
UserName935 earned 0 total points
ID: 39969239
Good Morning guru's,

OK, I think I solved this issue.  As I said before, I really rather stink with excel and tend to excel with access and sql.
Here is the solution to this issue.
1. Link the spreadsheet as a table to access
2. Write a make table query to make an internal table out of the spreadsheet
3. Create a form based on the new table
4. write a series of update queries against the new table using the values in column2, or field 2 as the criteria for placing data into the table.
5. write additional update queries based against the other data sources
6. Complete an export process replacing the old data in the workbook with the updated data from the DB table that was updated.
Done deal...
for those who helped, thank you very much...
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39969420
UserName935, apologies that I was not able to keep up with your time expectations; your comment asking if this was impossible was received in my UK local time just before 10:00pm; well into the evening and just before bedtime so I was not in a position to respond further.

I know EE prides itself in speed of responses but we are all volunteers on EE and I don't know about other experts but I do have a day job to deal with as well so I pretty much deal with questions that I am monitoring in my break time but was not able to look at this until lunchtime.

Maybe if you had expanded on the detail in the first place, like you did with your comment 39966982 then maybe it could have been dealt with quicker.

However, it sounds like you have been able to find the resolution yourself, which is good as every day is a learning experience.

Regards
Rob H
0
 

Author Comment

by:UserName935
ID: 39969934
Good Afternoon,

Relax, it's all good.

Sunshine and Light.

Thank you,
0
 

Author Closing Comment

by:UserName935
ID: 39981032
I figured it out.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now