Exporting The Results Of A SQL Statement Into A Table

Oct 10, 2005

I am going to attempt to explain the problem:

I design a form and all my work is based on that form.
I have two tables which I join together as following.
The unfitex field is calculating using fields from both tables as shown below.

Dim db As Database
Dim rst As DAO.Recordset
Dim sqlstr As String
v = Combo37.Value
sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); "
Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset)
Set Form.Recordset = rst

I then set up some textbox which shows the results as following.

N1.ControlSource = "N1"
Age.ControlSource = "age"
UNFITpc.ControlSource = "unfitpc"
DECENTpc.ControlSource = "decentpc"
HHSRSpc.ControlSource = "HHSRSpc"
UNFITex.ControlSource = "numunfit"
UNFITex.ControlSource = "unfitex"
DECENTex.ControlSource = "decentex"
HHSRSex.ControlSource = "HHSRSex"

It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it.
Can you help??

Thanks for your help

View Replies


ADVERTISEMENT

Looking For Example Of Exporting Query Results To Excel Template

Jan 27, 2008

Hi all,

I'm not very experienced with Access. I'm looking for a very simple sample of how to export Query results to an Excel Template document. Especially to specific cell references. Would anyone have one to share. I'm trying to get my head around the concept first.

I may have to use Access 2000 and later versions to run the database, so do I have to cater for both DAO and ADO?

Thanks in advance for any help
rgs
Ginny

View 2 Replies View Related

Macros For Exporting Search Results Into Excel?

Oct 9, 2015

I just wanted to export the search results into excel files. I have googled about it and I found the VBA code for exporting, which didn't worked out for me.

I used Macros by referencing one of the tutorial and it looks fine. Does the usage of macros for exporting the data into excel is also the correct procedure?

I just wanted to know that am I on the right track?

View 1 Replies View Related

Using Sql Results In IF Statement

Nov 22, 2005

I am running VBA that "deletes" a record by putting a 1 in a byte field called "deleted_b". But first it checks to see if the record is referenced anywhere in the database. So it may not actually mark the field.

There is no error if the "deletion" fails - the stored procedure runs regardless.

So I want the following code to run - basically it checks to see what the deleted_b value of the record referenced (Call_Attorney_ID) is. The problem is, I think I'm going about it incorrectly because I get a warning saying that a function or variable is expected in the IF statement - instead of the command to run the sql. Could someone tell me how to fix the code below to be the correct syntax?


Code:Dim mySQL As StringmySQL = "SELECT Deleted_B FROM dbo.MED_Attorneys_T WHERE Attorney ID = Call_Attorney_ID" If (DoCmd.RunSQL(mySQL)) = 0 Then MsgBox "Deletion failed. Record in use.", vbOKOnly Else Me.Requery MsgBox "Deletion has been completed", vbOKOnly End If

View 11 Replies View Related

Join Two Fields In Select Statement Results

Apr 12, 2006

I'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.


Let's say that I have 2 fields in MyTable: FirstName, LastName.

I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.

I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe

Thanks,
CRhodus

View 1 Replies View Related

Modules & VBA :: Displaying Results Of A Select Statement?

Oct 7, 2014

I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:

One Lender to Many Relationship IDs
One Relationship ID to Many Tax IDs
One Tax ID to Many DocumentsRequired

My forms are nested as follows:

Relationship ID form (contains info for Relationship ID and Lender)
-> Tax ID
-> DocumentsRequired

I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have.

Dim strtext As String
Dim strsearch As String
strtext = Me.SearchBoxTxt.Value
strsearch = "SELECT [Relationship ID] " & _
"FROM CustomerNormQuery " & _
"WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _
"OR [EIN/SSN] Like ""*" & strtext & "*"" " & _
"GROUP BY [Relationship ID]"
Me.RecordSource = strsearch

View 4 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 Replies View Related

Exporting Metadata (table Field Descriptions) With Table

Nov 22, 2005

Hello everyone,

I frequnetly need to export tables for others to use. I export them as .csv files usually and I'd like a way to include what I call metadata in the .csv file.

ideally, it would appear on the top of the file before the comma-delimited data actually starts.

I can't figure out how to include the table field descriptions in a file.

any help?

thanks,
giffordpinchot

View 10 Replies View Related

Exporting Table To Csv

Sep 21, 2007

Hi all.

I ave a table in access that has a column in the format single with auto setting for decimal places (I'm guessing this is 15 sig figs but I'm not too sure). What I'm trying to do is export this file as a csv so I can use it SAS. When I use the export function and select csv it truncates the data so all i get is 2 decimal places.
I can't export as a excel then rename as the file 1.4 million records long and so is too big for excel to handel. (I did try it anyway and it only exported the first 65000 rows).
I'm not an expert at all in Access, and would have little idea how to write any code that would allow me to do this.
Can anyone please help me as I urgently need to do this.

Thanks a lot.

Menes

View 2 Replies View Related

Exporting A Table To Excel

Nov 15, 2006

Hi all, I use a d'base that exports a table via a query to Excel. Once the user hits the button to run this command it can take between 30 sec to 60 sec until the data is displayed in excel.

Does anyone know of anyway to speed this process up. Currently there are around 12,000 records with the table.

DoCmd.OutputTo acOutputQuery, "qryArchiveReport", acFormatXLS, "Archive.xls", True

thanks for looking:)

View 1 Replies View Related

Exporting Table To DBase IV (*.dbf) With VBA

Feb 22, 2008

Hello guys,

I am working on a database that requires a user (with very little access knowledge) to export a table into .dbf (DBase IV Format) using a button and VBA.

I realize this can be done by right clicking on a table and click export. However I did not give the end user access to the tables (being a good DB Developer!) and also I don't believe I can teach this particular user how to do the steps to accomplish this.

The scenario: Third party software needs (SwissSys, which is a chess software) to grab information about players and ratings from a .dbf file (and will not work with access). I have developed a database for the association so that they can edit players information, and update the ratings directly off the website (www.chess.ca) and it automatically updates in the database.

The idea is, the Access Database updates the ratings from the website, and then the user exports that data into the dbf file (players table) that is directly linked to the third party software.

I am looking for a way to use VBA code to either 1. Create/Overwrite the existing dbf file when exporting the tblPlayers, or 2. Append/Update the current dbf file with the updated ratings, and creating new entries when necessary when exporting the tblPlayers.

Unfortunately I have had little success in finding code to export to a dbf file, anyone have any ideas?

View 3 Replies View Related

Exporting Linked Table

Aug 24, 2004

Hi there i am exporting a table that has linked tables to others (ie the "+" you get when you link the tables) but when i export the table ... the linked tables dont get exported.... can someone be kind enough to help with this

View 6 Replies View Related

Exporting Specific Record In A Table

May 2, 2006

I need to export a specific record in the table. The menu's export option only export the entire table. Is there a way to define certain record to export?

Thanks

View 1 Replies View Related

Universal Path For Exporting Table?

Aug 5, 2014

I've created a macro that will export one of my tables to a .csv. Everything is all fine and dandy, except that I need to find a way to have this command work on any computer. As of now, the command exports to C:UsersKodyDocuments, and can only do so because this path exists on my computer. But if I were to send this Access file to someone else to use, they would get an error stating that the specified path is not valid. Is it possible to create a general export directory which would work with any computer this is ran on?

View 5 Replies View Related

Exporting Multiple Csv Files From A Single Table

Mar 19, 2008

Hi guys. I'm a bit of a noob with Access. I have a table which is 15 rows by 250,000 columns. So there are about 3.75m records.

What I need to do is to get access to export a number of csv files automatically from this table.

I need each csv file to contain all the data in the table for a certain range of rows. This is determined by a value in a particular column. This is a sample of the table.

http://i30.tinypic.com/aljf5s.jpg


Basically Store_Nbr represents a UID for a particular store. All the data is currently in one big table, and I need a single csv file for each different store_nbr (so it contains all the data in the table for each unique store). Also, for each store_nbr, there are different Year Month of Surveys. For each store_nbr, I need a different csv file for each Year Month of Survey. This will probably divide up the 250,000 long table into about 200 csv files, as there are about 50 different Store_nbrs, and there are 4 surveys for each individual store.

Sorry if I've not made myself perfectly clear.

I'm sure there's a simple way of doing it, but I really do suck with access and was wondering if you guys had any idea.

Many many thanks!!!

Cheers

View 4 Replies View Related

Setting Table Field To True When Exporting To Csv

Sep 21, 2006

Hi,

I export a query as csv to upload to another database on a web site. When that csv file has been exported I'd like the query to be emptied, so that the next time I export I don't export duplicates. What's the easiest way to do that?

I've been thinking of having a field in the table with a check box that is checked when the file is exported. The query then selects only those records where that box is not checked.

But how can I check those boxes on Export rather than when I run the query? The reason being that I may want to preview by running the query, without having that affecting the check box.

View 1 Replies View Related

VBA Help : Exporting Column Names With Data From One Table To Another

Feb 12, 2008

I have an Access table say Tbl_People that looks like :

ID1-ID2-Name-Age-Location
xxx-yyy-Mike-25-Essex
uuu-vvv-Jack-32-Surrey
mmm-nnn-Bob-36-Newcastle

I want to transfer this data into another table say Tbl_Output with four columns in the format below:

xxx-yyy-Name-Mike
xxx-yyy-Age-25
xxx-yyy-Location-Essex
uuu-vvv-Name-Jack
uuu-vvv-Age-32
uuu-vvv-Location-Surrey
mmm-nnn-Name-Bob
mmm-nnn-Age-36
mmm-nnn-Location-Newcastle

In Tbl_Output's 3rd column, only the Columns names: Name, Age and Location are repeated for each person and not column names ID1,ID2 (only its data xxx,yyy etc. is required in columns 1 and 2 as shown).

I was helped by rpeare with a VBA module that gives a single column output in Tbl_Output as

Mike
25
Essex
Jack
32
Surrey
Bob
36
Newcastle

The code is:

Sub main()

Dim db As Database
Dim rstElements As Recordset
Dim sName As String
Dim sNumber As String
Dim sArea As String
Dim freefile
Dim Filenumber As Integer
Dim sSQL As String

Set db = CurrentDb
Set rstElements = db.OpenRecordset("tbl_elements")
rstElements.MoveFirst

sSQL = "DELETE * FROM Tbl_Output"
db.Execute sSQL

Do While rstElements.EOF <> True
sName = rstElements.Fields(1)
sNumber = rstElements.Fields(2)
sArea = rstElements.Fields(3)

sSQL = "INSERT INTO Tbl_Output (OutputField) SELECT '" & sName & "'"
db.Execute sSQL

sSQL = "INSERT INTO Tbl_Output (OutputField) SELECT '" & sNumber & "'"
db.Execute sSQL

sSQL = "INSERT INTO Tbl_Output (OutputField) Select '" & sArea & "'"
db.Execute sSQL

rstElements.MoveNext
Loop

Set rstElements = Nothing
Set db = Nothing

End Sub

How can this be modified to get the required format data above? Thanks for any help in advance

View 4 Replies View Related

General :: Exporting Table - Yes / No Instead Of True / False

Aug 2, 2012

Using DoCmd.TransferSpreadsheet I'm exporting a table which has a couple of Yes/No fields formatted as Yes/No. However when I export, they appear as TRUE/FALSE? Am I doing something wrong?

View 3 Replies View Related

Modules & VBA :: DAO Exporting Text From Related Table

Jan 4, 2014

I am trying to export text to a 2010 word document from an access 2010 database with DAO. I have successfully been able to export text from the main table tblLandSales via variables (below) and then subsequently a document. I used the following code:

Dim objWord As Word.Application
Dim docm As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strLandSalesID As String

[Code] ....

Now I want to export other data from a one to many related table where [fk_tblLandSalesID] is the foreign key in the related table and tblLandSalesID is the primary key in the main table.

View 5 Replies View Related

General :: Exporting Every Single Record From Table To PDF?

Oct 13, 2014

I need to export/print every single record from table as a pdf file (one record one pdf file). how to do this.

I'm working on Access 2000.

View 1 Replies View Related

General :: Exporting Table From Access As Text File?

May 1, 2015

When exporting a table from Access as a text file, it keeps adding .00 to the end of the number format records that I am tying to export.

Why it is doing that and what I need to do to prevent that from happening?

View 3 Replies View Related

General :: Table Names Change When Exporting To Excel

Mar 14, 2013

When exporting my tables from access to excel my table names change if they have a space in the name. Example table name "New Record" turns into "New_Record".

What I am trying to do is export the table data to excel than update my access program than imort the table data back. This way I can take a vertion of my access program and update/modify it as time permits than reinsert all current data with min down time.

The code I am using is as follows:

Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "excel_out"
Set db = CurrentDb()
For Each td In db.TableDefs

[Code] .....

View 14 Replies View Related

Exporting Table In Excel Format And Email As Well At Certain Time Of Day

Sep 6, 2012

I want to export a table in access table .

1. for example every day at 8.00 it create an output excel file.
2. Attach the file and send to a fixed email at that time.

View 1 Replies View Related

Exporting A Table To A Macro-Enabled Excel Workbook?

Apr 5, 2012

I am using Office 2007 and trying to export a table in Access to a Macro-Enabled workbook in Excel. Unfortunately, when I go to export the table, my file does not show when I browse for the file and .xlsm doesn't look like it's a supported file extension. I have looked around and noticed others have this problem as well. One solution was to use save the Excel file as a 97-2000 file since it doesn't change the file extension based on having macros. However, I can't do this because then I lose functionality with tables and other things on my spreadsheet. I need the data in Excel to be updated every month. Is there any way to do this in Access? I'm going to explore using sharepoint. My DB options are limited to Access and Sharepoint. I don't have access to Oracle or SQL Server or MySQL etc.

View 1 Replies View Related

Queries :: Exporting Query To Pivot Table Remove Subtotals?

Nov 19, 2013

I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?

View 1 Replies View Related

General :: Exporting Data From A Table Keeping Current Design

Sep 16, 2013

I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved