Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-21-2019, 11:50 AM   #1
rsingh4377
Newly Registered User
 
Join Date: Jun 2019
Posts: 39
Thanks: 9
Thanked 0 Times in 0 Posts
rsingh4377 is on a distinguished road
Access to Excel?

Hi, So I am trying to help add values to one database from another database. The origin of the data will come from a couple of tables on access and I am trying to input this information into an excel spreadsheet. However, the access data needs to be filtered before I transfer it to the spreadsheet. I am new to coding so I wanted to know if it was possible to filter the information with coding and then have this data inserted into excel? For example, I am trying to take a percentage from a couple of fields and divide this value by another field to get a percentage. So far I have this:
SELECT ((sum_quantity3+sum_quantity4)/(sum_total))*100
FROM ([tables]![bridge])
WHERE ([tables]![info]![Region]=1)
However, I am an amateur at coding so this may be entirely incorrect? Does it look like this would help me achieve what I am trying to do?

rsingh4377 is offline   Reply With Quote
Old 06-21-2019, 11:59 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,602
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access to Excel?

Hi. Does your query work? Meaning, it gives you the information you want to export to Excel? If so, you could try several approaches to create an Excel sheet from the query. For example, there's a macro action to "export" the query to Excel, there's a method to "output" the query result into other file formats, such as Excel, and you can of course use code to do it as well.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-21-2019, 12:44 PM   #3
rsingh4377
Newly Registered User
 
Join Date: Jun 2019
Posts: 39
Thanks: 9
Thanked 0 Times in 0 Posts
rsingh4377 is on a distinguished road
Re: Access to Excel?

My query is not functional, I may be doing something wrong I'm assuming. Does my code look like it would work? I guess out of context it can be a bit puzzling? And that's great I just took a look at that! Thanks so much for the tip! Should I upload a sample in case I am unclear about my situation?

rsingh4377 is offline   Reply With Quote
Old 06-21-2019, 12:48 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,602
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access to Excel?

Quote:
Originally Posted by rsingh4377 View Post
My query is not functional, I may be doing something wrong I'm assuming. Does my code look like it would work? I guess out of context it can be a bit puzzling? And that's great I just took a look at that! Thanks so much for the tip! Should I upload a sample in case I am unclear about my situation?
Hi. A sample file always helps. In my humble opinion...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-24-2019, 06:14 AM   #5
rsingh4377
Newly Registered User
 
Join Date: Jun 2019
Posts: 39
Thanks: 9
Thanked 0 Times in 0 Posts
rsingh4377 is on a distinguished road
Re: Access to Excel?

Hi, sorry for the late reply. Hope you enjoyed your weekend. I am posting a sample database with fields from my current database but only in fields that I need for the access to excel transfer. Basically I am trying to take certain fields and create a calculation and transfer this data to an excel sheet. Is this even possible? Please let me know, thank you!
rsingh4377 is offline   Reply With Quote
Old 06-24-2019, 06:24 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,077
Thanks: 81
Thanked 1,600 Times in 1,484 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Access to Excel?

rsingh, nothing was attached to your post.

As to whether it is possible, you can CERTAINLY use a query as the source for a transfer. In the article I am linking below, the "table" argument can also be a query.

https://docs.microsoft.com/en-us/off...ferspreadsheet

If you want to send a computed field as part of the operation, just understand that when it gets to Excel, it will be a value, not a formula, because the computing would be done within Access and THEN the transfer would occur.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 06-24-2019 at 06:38 AM.
The_Doc_Man is offline   Reply With Quote
Old 06-24-2019, 06:27 AM   #7
rsingh4377
Newly Registered User
 
Join Date: Jun 2019
Posts: 39
Thanks: 9
Thanked 0 Times in 0 Posts
rsingh4377 is on a distinguished road
Re: Access to Excel?

Thanks so much for the link, I will take a look...and sorry I'll post the sample with this post. And that would be perfectly fine, just wanted to know if that is possible and how I can achieve this.
Attached Files
File Type: accdb AnotherSample.accdb (640.0 KB, 6 views)

rsingh4377 is offline   Reply With Quote
Old 06-24-2019, 07:23 AM   #8
rsingh4377
Newly Registered User
 
Join Date: Jun 2019
Posts: 39
Thanks: 9
Thanked 0 Times in 0 Posts
rsingh4377 is on a distinguished road
Re: Access to Excel?

Just wanted to clarify what I am looking to do. I want to take information from both tables in the sample and use certain operations to filter the results into excel. As you can see in the sample one table has duplicate BIN entries as records with different elements. Each element affects different BIN and I basically want to take a percentage (if you scroll further) from only cs3 and cs4 out of the total sumquantity and put this information into excel based only from region 1 entries which can be seen in the other table. Please let me know if this makes sense or if I should clarify.
Attached Files
File Type: accdb AnotherSample.accdb (640.0 KB, 3 views)
rsingh4377 is offline   Reply With Quote
Old 07-11-2019, 10:35 AM   #9
KHallmark
Newly Registered User
 
Join Date: Jul 2019
Posts: 12
Thanks: 0
Thanked 1 Time in 1 Post
KHallmark is on a distinguished road
Re: Access to Excel?

In Excel, Click on the Data Tab, and then click on the 'From Access' button on the left side of the screen, select your database and it will go into the excel document. Depending on the number of cells in your Access table, i would reccomend you create a query with just the info you need.

after that, open Excel, and create a second table in the workbook. using formulas you can link to the imported table's Cells to create the report you need.

KHallmark is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access to Excel to Iphone 6 Plus with Excel and back to Access Mike375 General 21 02-12-2016 08:27 PM
opens an excel file and copies values from access textboxes to excel cells deanvilar Modules & VBA 3 05-27-2013 08:27 AM
appending table in access from excel, excel file name change everyday with date gcgc2000 Queries 1 12-28-2012 04:21 PM
Import Excel workbook into Access table using Excel's format (formulas, values, etc) captdkl02 Modules & VBA 1 12-17-2012 09:03 AM
import multiple excel files with FILTERED Excel spreasheet into an access table WuJu Modules & VBA 5 10-31-2011 04:38 PM




All times are GMT -8. The time now is 12:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World