Access to Excel? (1 Viewer)

rsingh4377

Registered User.
Local time
Today, 02:40
Joined
Jun 17, 2019
Messages
39
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:40
Joined
Oct 29, 2018
Messages
11,217
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.
 

rsingh4377

Registered User.
Local time
Today, 02:40
Joined
Jun 17, 2019
Messages
39
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:40
Joined
Oct 29, 2018
Messages
11,217
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...
 

rsingh4377

Registered User.
Local time
Today, 02:40
Joined
Jun 17, 2019
Messages
39
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!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 02:40
Joined
Feb 28, 2001
Messages
17,342
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/office/vba/api/access.docmd.transferspreadsheet

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.
 
Last edited:

rsingh4377

Registered User.
Local time
Today, 02:40
Joined
Jun 17, 2019
Messages
39
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.
 

Attachments

rsingh4377

Registered User.
Local time
Today, 02:40
Joined
Jun 17, 2019
Messages
39
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.
 

Attachments

KHallmark

Registered User.
Local time
Today, 00:40
Joined
Jul 11, 2019
Messages
12
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom