Help with SQL (Access2007)

Hello.

I am trying to integrate data from several sites into 1 (new) table. In 
order to distinguish the data from each site in the new table I have a field 
(InstID) which holds the Instution number of the site. The fields from the 
old site tables and the new table are identical except for the InstID. 
InstID and ClientID are Primary Keys.

The path to the old table is asked, then the number for the InstID is asked 
and placed as a variable - varInstID.

I have an append sql as follows:

Private Sub UpdateDB_Click()
' populate the clients table
strSql = "INSERT INTO tblClients ( InstID, ClientID, RegistrationNumber, 
Active, FirstName, LastName, Address, City, Province, PostalCode, HomePhone, 
BirthDate, RegistrationDate) " & vbCrLf & _
"SELECT tblClients.ClientID, tblClients.RegistrationNumber, 
tblClients.Active, tblClients.FirstName, tblClients.LastName, 
tblClients.Address, tblClients.City, tblClients.Province, 
tblClients.PostalCode, tblClients.HomePhone, tblClients.BirthDate, 
tblClients.RegistrationDate, * " & vbCrLf & _
"FROM tblClients IN '" & strName & "';"
dbs.Execute (strSql)

.... <snip>

How do I get the varInstID to be placed in the InstID field?

Thanks.

Rick 

0
Rick
2/27/2008 9:10:04 PM
access 16762 articles. 3 followers. Follow

2 Replies
656 Views

Similar Articles

[PageSpeed] 53

Hi Rick,

You simply need to concatenate the value for the variable varInstID into the 
SQL string; specifically, it needs to go into the Select ... From clause in 
the first position.  If InstID is a number, the line should be:
....
"SELECT " & varInstID & ", tblClients.ClientID, 
tblClients.RegistrationNumber,
....

If InstID is a text field, the line should be:
....
"SELECT '" & varInstID & "', tblClients.ClientID, 
tblClients.RegistrationNumber,
....
Expanded for clarity, that's:
"SELECT ' " & varInstID & " ', ...


HTH,

Rob

"Rick" <noway@notme.com> wrote in message 
news:uSrEgUYeIHA.748@TK2MSFTNGP04.phx.gbl...
> Hello.
>
> I am trying to integrate data from several sites into 1 (new) table. In 
> order to distinguish the data from each site in the new table I have a 
> field (InstID) which holds the Instution number of the site. The fields 
> from the old site tables and the new table are identical except for the 
> InstID. InstID and ClientID are Primary Keys.
>
> The path to the old table is asked, then the number for the InstID is 
> asked and placed as a variable - varInstID.
>
> I have an append sql as follows:
>
> Private Sub UpdateDB_Click()
> ' populate the clients table
> strSql = "INSERT INTO tblClients ( InstID, ClientID, RegistrationNumber, 
> Active, FirstName, LastName, Address, City, Province, PostalCode, 
> HomePhone, BirthDate, RegistrationDate) " & vbCrLf & _
> "SELECT tblClients.ClientID, tblClients.RegistrationNumber, 
> tblClients.Active, tblClients.FirstName, tblClients.LastName, 
> tblClients.Address, tblClients.City, tblClients.Province, 
> tblClients.PostalCode, tblClients.HomePhone, tblClients.BirthDate, 
> tblClients.RegistrationDate, * " & vbCrLf & _
> "FROM tblClients IN '" & strName & "';"
> dbs.Execute (strSql)
>
> ... <snip>
>
> How do I get the varInstID to be placed in the InstID field?
>
> Thanks.
>
> Rick 

0
Rob
2/27/2008 9:26:02 PM
Thank you very much Rob. That worked.

I had put the concatenated variable into the Insert Into instead of the 
Select clause. Too much time in front of the screen I guess. Duh.

... rick 

0
Rick
2/27/2008 9:52:47 PM
Reply:

Similar Artilces:

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

Messages not delivered to another Routing Group HELP NEEDED
I have three routing groups ( exchange in the 3 of them are Exch 2k sp3 ). Mail from one server ( in my headquarter routing group ) to another ( in one branch office routing group ) is getting stucked in MESSAGES WITH UNREACHABLE DESTINATION and not routed to that server. Connectors seems to be working just fine. After add my other exchange server from my headqurter group to the connector to be albe to send mail it shows the queue now in the connector but does not sends anything. Otherwise, mail from my branch office to my headquarter correctly sends mail. Any help would be gre...

Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?
Any suggestions? Some of these rows only need a few columns updated, and others need all of them updated. If possible if say row 800 column 'S' is filled in the access DB but is blank on the excel spreadsheet, if possible i like to keep the existing data in access and over right it with a blank cell. The spreadsheet is a export of the access DB, changes were made/merged from other spreadsheets so the Unique ID is the same and in column A. A script suggestions to complete this? Thanks :) dim dbe dim db dim ssql set dbe = createobj("dao.dbengine.36") set db = dbe.openda...

Install problem, can't locate SQL instance same machine
I'm trying to install Action Pack's CRM software on my server in-office. I can't even get the silly thing to find SQL and its the same machine!! What gives!?! The server is Win 2000 AS /SP3 and is a DC, but not any of the FSMO role owners. It also has exchange running it. I go about 3 screens into the install where it asks to select my SQL and create new or use existing databases. I try typing in the UNC for my server and say next, it sits the for about 15 seconds and says failed to authenticate/rights issue. I'm logged in as domain admin, and SQL security is set at Windo...

Need help with Combo Box?
I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. Sure...

SQL Query
I'm trying to create a query that will result in receipt transaction details from POP10310 combined with GL transactions from GL20000 and so far no success. I assume I also need POP10300 in the mix. Which field(s) should I be joining between GL20000 and POP10300 or PO10310? Thanks. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL Try using POP30310 instead. The receipt transactions would have to be posted before anything got to GL20000. -- Jim@TurboChef "Frank Hamelly, MCP-GP" wrote: > I'm trying to create a query that will result in receipt transaction &...

SQL backup corrupted or unreadable
I did a backup of a 36GB SQL database to a USB hard drive, which completed successfully. When I went to copy that backup to my archive server, at around 40% it errored aaying the file was unreadable or corrupted. Of course, I had already deleted the database since I needed room on my server and thought I had a good backup. I've tried everything I know, including trying to undelete the databse files from the server, which found nothing and running chkdsk on the USB drive. Of course, when I did that, it showed the file as having 0 KB! So am I hosed or what? is there anything e...

Copying Data From SQL Into Excel
When I copy a range of data from SQL and paste it into Excel, the data doesn't appear to be available for formulas - in this case a VLOOKUP formula. However, once I click in the formula bar (as if to edit the data) then hit "return" the data is "magically" available for the VLOOKUP formula. It seems to me to be a format problem, but changing the format of the data doesn't help. Could this be related to similar issues when copying data from Access into Excel? Are there any workarounds? It sounds similar to the Access problem. Instead of pasting, you can ...

XML vs. SQL Database
I am developing a home project to be later released as an online application. I am currently using XML to store game data, user/member data, and other. Assuming a user base of 10,000 active users, is there a conflict if one user is updating information and another user is creating data on the same xml file? For example: User 1: update inventory that exists in the XML file User 2: new user adding their inventory data in the XML file Thanks for your help. Michael "Michael Jesse" <michaeljesse1973@sbcglobal.net> wrote in message news:tUVDd.13357$iC4.7938@newssvr30.news....

SQL to update Item.LastCounted based on PhysicalInventory.CloseTime
Because of a bug/deficiency/oversight in a third party inventory counting application , I did a bunch of physical inventories that did not update the Item.LastCounted field. I need this field updated so I can see the correct date each item was counted last. I am willing to do a seperate SQL statement for each physical inventory sheet for which I have the problem. For example, I want to take each item in the PhysicalInventoryEntry table with PhysicalInventory.Code = '0000057' (or whatever the code is) and set Item.LastCounted = PhysicalInventory.CloseTime. I am not great at SQ...

Help making BINGO cards
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am quite the novice at using Excel. I'm trying to create a set of BINGO cards for a church group and I'm having trouble figuring it out. I saw a post about something called RAND function but I don't know what that means. <br><br>What I need is 56 unique cards with 5 columns and 5 rows each. The cards will be using the numbers 1-24 leaving the center square blank or &quot;0&quot;. <br><br>Can anyone please assist? This is a multi-part message in MIME format. ----...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

REALLY NEED HELP
Hi guys, i'm hoping someone could lend me a hand. I'm setting up an excel file to input our fees received from our customers sent to collections. I have a main page with all the customers names, and each name is a hyper link to that customers separate worksheet - where a running tab is kept. I have a button called "update" and i've assigned the following macro (also called UPDATE) move the info over to the respective customers worksheet. I'm trying to get a loop going. A# is the customers name (first name will be starting at A4. B# to F# (first transaction...

Workbook there but not visible! Help!
I was working on some VBA code for an excel application I am writing. I switched between the VBE and the spreadsheet and all of a sudden my workbook is no longer visible! In the VBE project explorer window it shows my project and my code but in the excel window...no workbook! Nothing! Ran a test procedure calling IsAddin to see if somehow I accidentally clicked a button/box telling Excel to make this file an addin but it returned false. I did close the file and re-open it. Any ideas? Maybe it's just off the visible screen: Window|Arrange|tiled (and resize manually) or maybe y...

Please help Password Trouble
I am using outlook 2000 on a DSL line on XP Home. I have chosen to not save my password, and the problem that I am having is that everytime I go to check my mail it makes me type in my password. There must be a way (like in express) that allows you to just type in your password the first time you log into outlook and keeps you logged in until you decide to log out?? Thanks for your help!!!! -- Virtualliance, Inc. Mark Needham 7 Kimball Lane Bldg A Lynnfield, Ma 01940 T 001-781-224-4700 F 001-781-224-2414 C 001-617-799-4597 www.virtuallianceinc.com mneedham@virtuallianceinc.com im: vaincmar...

Please help....
I have a question regarding bank reconciliations that I am hoping someone can help me with. It concerns a USD bank account that I use. At the end of the month I prepare the bank reconciliation in GP. After completing the reconciliation I get a print-out called the "Reconciliation Posting Journal". This print-out provides me with the folowing: Bank Statement Balance Oustanding Cheques(-) Depoits in Transit(+) =Adjusted Bank Balance All of these amounts are in USD. Then I go to "Financial - Inquiry - Summary" and pull the summary balance for this GL ...

Help! Lost all email from inbox
Hi, hoping someone can advise me. Somehow my fingers fumbled when reaching for mouse to open an email and every email in my inbox disappeared. I have outlook 2000 and do have it set to automatically delete when i highlight and press delete key. But in this case, i didnt highlight anything and must have hit some combination of keys and poof - over 100 emails from inbox disappeared. They are not in deleted items folder and many were not even opened yet. tia kate <kate@discussions.microsoft.com> wrote: > Hi, hoping someone can advise me. Somehow my fingers fumbled when >...

Help! New to Publisher
What is the A...at the bottom of a page inside a small box? I am typing a newsletter. Shouldn't one page flow to the next like in Microsoft Office? If it is text overflow, I cannot retrieve. What am I doing wrong? Please advise. That indicates that there is more text inside that area that cannot be seen. If you stretch that text box down, you'll see the rest of your text. To make it flow from one box to another, you need to set it up for that. (I will admit that I break mine manually because I've never taken the time to learn how to do it correctly.) -- The problem with ...

Outlook 2002 backup question...please help!
Hi all, I am having a problem with windows which may require me to reformat. I cannot get into outlook (2002) via windows, but I can access files from dos. Can someone please tell me where the data files for outlook 2002 are stored, and which ones I'd need to copy? Thanks in advance for any help. Jim See if this info helps: http://www.howto-outlook.com/howto/backupandrestore.htm "Jim" <lakerfan426@yahoo.com> wrote in message news:OUhIH0oTGHA.4132@TK2MSFTNGP11.phx.gbl... > Hi all, > > I am having a problem with windows which may require me to reformat. I >...

Need Help Using A Custom Session Manager
Hi. I have some questions about session management. I have decided to use a custom session manager class to have more control over session state in my web site. I have started out by using Stephan Prodan's Session Class here: http://stefanprodan.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3DC%2523 You'll need to take a look (which I appreciate immensely) to get a sense of what I am talking about. 1) My first question pertains to how I persist and access my session information after a user authenticates (or doesn't). In his exam...

Help: MAPI can't find PSTPRX.DLL
Hi there Can anyone suggest how I can make outlook 2002 work properly. Everytime I press send/receive it says MAPI can not locate PSTPRX.DLL. I have done search and it is not on my computer. Please can anyone assist? just trying out 1st time >-----Original Message----- >Hi there > >Can anyone suggest how I can make outlook 2002 work >properly. Everytime I press send/receive it says MAPI can >not locate PSTPRX.DLL. I have done search and it is not >on my computer. Please can anyone assist? >. > See if this info helps: http://support.microsoft.com/default.asp...

How to execute a parameterized Sql store procedure within Excel?
How to execute a parameterized Sql store procedure within Excel and pass the Excel cell value as the parameter(s). Need to look at sample to understand the concept, if any. Many thanks. hi, Paul ! > How to execute a parameterized Sql store procedure within Excel and pass the Excel cell value as the parameter(s). > Need to look at sample to understand the concept, if any. Many thanks. see if any of the following is what you want... Ed Ferrero: Run SQL Server stored procedure in Excel macro -> http://tinyurl.com/pdmg2 Robin Hammond: Stored procedures -> http://tinyur...

Help with formatting text in a textbox
Hi All, I have been trying everything I can think of, and still not getting the results I'm after. I am trying to summarize an order in a textbox, by building a string and assigning it to the caption property of the text box. I create a recordset based on what's in the table, then loop through it and build my string(s). I want to display everything nicely, so I am trying to space each field so that I can put in a header row of labels, and then have each record show nicely spaced below. I am now finding that even when I take into account the length of the data inthe field, I still ca...

search help
I have a form which contain 2 unbound text boxes and a subform. 1st text box is where I input client's ID and 2nd textbox is for client's name. I have set the child and master field for subform to clients ID on subform and 1st testbox on main form. This works fine if I only have to search by client's ID. My problem is how do I make the subform to requery if ,say the client forget his ID # or search using ID produce no result, and I have to search by name using 2nd textbox as a search string. I tried changing the master and child field link using code but that didn't work...li...