Windows Support Forum

Comparing Access table data

Q: Comparing Access table data

Real dummy here using Access. I have two tables containing 80,000 names. These tables are going to change weekly. What I need is a query that will compare new user in table 2 to the ones in table 1. Then I need a query that will show the users that are not in table 2 that were in table 1.Hope this makes sense.

Relevancy 100%
Preferred Solution: Comparing Access table data

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Relevancy 76.11%

Basically I have a situation where users are creating data tables in Microsoft Word using the creating table functionality, and are then resquesting that the data will be imported into a single Microsoft Database field.

Please could someone show me the best way of doing this!!!!!!

Cheers !!!

A:Importing Table Data Into Multiple Access Field Data?

Do you mean they're creating, say, a 3-row by 3-column table and then they want to import everything in that one table into a single field? a single record?

Out of curiosity, what happens if you copy and paste, perhaps with an intermediate step through Excel?

Also, is there a reason why your users cannot work directly in Access?

More details, please.
Relevancy 75.25%

Hello I ve been struggling with getting a simple InfoPath form to work with a test MS Access database I feel the best way to explain what I am doing is by giving you an example Background of scenario Lets say I am a distributor of gas cylinders I have different gas cylinders that I fill and send to customers When they are done with the gas they return the cylinders and I fill them back up Each cylinder has specific data related to each manufacturer serial specific cylinder ID and material of construction carbon steel stainless steel etc I have this data in my MS Access database in an individual table called quot CYLINDER DATA quot Every time I refill a cylinder I need to record the from another table, (same - table data 2007 one Retrieve data submit to InfoPath data date and measure the pressure and temperature among other things of the gas InfoPath 2007 - Retrieve data from one table, submit data to another table (same data in the cylinder This data along with the specific quot CYLINDER DATA quot at this date of measurement must be added as a new record in a different table called quot CYLINDER MEASUREMENTS quot in the same database What I would like to do So I want to create an InfoPath form for operators to use whenever they need to perform measurements on a newly filled cylinder So I created an InfoPath form that starts with a drop-down list box control that is populated with the different cylinder IDs A user would choose one of the quot cylinder IDs quot from the drop down list and then click quot retrieve data quot This would then populate the fields directly below manufacturer serial material of construction etc from the quot CYLINDER DATA quot table Below this section would be a section to quot fill in quot measurements to the quot CYLINDER MEASUREMENTS quot table It would have blank fields for quot pressure quot quot temperature quot quot date quot and other measurement fields Once filled in the operator would hit quot submit quot and a new record in the quot CYLINDER MEASUREMENTS quot table would be created This new record would included the everything populated on the InfoPath form the cylinder specific data AND the measurements on the given date What I ve tried doing I have my database set up in the two tables outlined above I created an InfoPath form with the quot CYLINDER DATA quot as the Main Connection I am easily able to retrieve cylinder specific data from the CYLINDER DATA table into my form by use of the queryfields and datafields However I cannot seem to get the quot submit quot to go to the quot CYLINDER MEASUREMENTS quot table The form seems to only want to submit to the quot CYLINDER DATA quot table I also tried making the quot CYLINDER MEASUREMENTS quot table the Main Connection so I could submit to it But then it seems that I cannot create a database query to quot retrieve quot data from the quot CYLINDER DATA quot table Where to go from here I am looking to get any insight from you to see how I can configure this appropriately to get what I need I also do not want the user to be able to change the data in the quot CYLINDER DATA quot table so I would likely have those fields quot read-only quot Any information support is appreciated Thank you nbsp
Relevancy 72.24%

I have a table in Access that contains 2 fields, ID and CODE. I have another spreadsheet that contains ID's for which the user needs to get their code again. Is there anyway that I can create a query that will take the ID's from the Spreadsheet, Look them up in Access, and then assign the correct code back to the spreadsheet?

A:How do Get data from One table in Access To another?

Spreadsheet or database table? Are both tables going to be "permanent"? If so, then "copying" this data is not appropriate normalization on the database and you'll just be "bloating" your data.

Your database is not going to be normalized properly if you plan on using two of the same exact fields in two separate tables unless you are using a lookup to obtain them. If you need more info, email me requesting the normalization.doc file.

Meanwhile, if you just need to get it out to a table or spreadsheet, just create a query on that table that grabs those two columns. When you're sure this query contains the exact data you want, open it again in design view and hit Query-Make table query (an "action" query). Now, when you run the query, it will create a new table. You can then go into the design of this NEW table to add other fields as necessary.

Hope this helps.

Brainbench MVP for Microsoft Word
Relevancy 71.81%

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.

Relevancy 70.52%


Within Outlook, I am looking to add a Toolbar button that calls a vba procedure that does the following:
-Prompt user for a code
-Find the code in fldCode in tblLookup in an Access database
-Fill in the To, Subject, Body, etc in a new mail message based on contents from the tblLookup

Any ideas. I have been scratching my head in defeat for quite a long time - any help would be truly appreciated!!

A:Outlook VBA to fetch data from Access table

This should get you started. Make sure you add in the 'Microsoft ActiveX Data Objects 2.8 Library' (I added 2.8, other versions may work) by opening Outlook Visual Basic and going to 'Tools', 'References'.

Create a Macro in Outlook and code it to connect to your database something like this:

Sub GetMyData()
Dim myCn As New ADODB.Connection
Dim myCmd As New ADODB.Command
Dim myRs As New ADODB.Recordset

Set myItem = Outlook.CreateItem(olMailItem)

myCn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=databasename.mdb;" & _
"DefaultDir=databaselocation (c:\whatever\whatever);"

Set myRs = myCn.Execute("Select * from tablename")

'Replace field names in my example below to match your database fields
myItem.To = myRs.Fields("EmailAddress")
myItem.Subject = myRs.Fields("Subject")

'Be sure to close the recordset and connection when you're done
Set myRs = Nothing
Set myCn = Nothing

End Sub

After you create that macro, you can add it as a button to a toolbar. In Outlook, select 'Tools', 'Customize', 'Commands' tab, 'Macros'. Drag your macro to the toolbar.

Hope this helps.
Relevancy 70.52%


We are currently running access 97, we have a database table created, but when we enter on the form that we have created the date and time does not show up on the table. We have on the form the date and time automatically set. Is there a way to have it transfer to the table??

A:Access 97: Transfer Data From Form to Table

Sounds like you don't have that form control linked to the table. I'm not sure how to do it off the top of my head, but you can try right-clicking the data field in form design view, and go to the Data tab and check out the control source. It's gotta be something like tablename->field. If you hit ctrl-F2, you should be able to "build" it. It may be helpful to you to look at how another control in the form is set up, then duplicate it, but change it to the date field.

(Maybe that'll give you something to do until the troops arrive.)
Relevancy 70.52%

Hello OBP and others,

I have 3 Access XP survey forms that are not displaying the uploaded data in the tables, though they are restricting the number of records to the number of surveys in entered into the tables (as they should be doing). The "Data Entry" settings are set to NO for all forms and subforms. Do you have any suggestions as to how this can be fixed?

Thank you,

Relevancy 70.52%

Help-I am trying to do a task for my boss we have code that transfers information from one table to another once a date has been populated it looks like this ----------------------------------- Private Sub Fund Date AfterUpdate On Error GoTo err Fund Date AfterUpdate Dim db As Database Dim rs As Recordset Set db CurrentDb Set rs db OpenRecordset quot tblProjects quot DB OPEN DYNASET to table annother one from Access-transfer data MS rs AddNew rs Reference Number Me WO rs Title Me Title rs Original Cost Me FundAmt rs Bldg Me Bldg rs PM Me Owner rs Project Type quot New quot rs Update Me WO SetFocus MsgBox quot Update the NEW Project Record quot exit Fund Date AfterUpdate Exit Sub err Fund Date AfterUpdate MsgBox Error Resume exit Fund Date AfterUpdate End Sub ----------------------------------- There is another piece of data coming from the same location that needs to be populated in another table the location is quot tblAdditionalData quot and the field is quot Comments quot I tried putting rs Desc Me Comments but of course that did not work because it is a different table What can I add to to make this happen Thanks Joe nbsp

Relevancy 70.52%


I am using Access to hold data. The table in Access runs across from right to left. I want to copy the data that runs from right to left and paste it into Excel from top to bottom ( up and down). Is there a way to do that without doing one field at a time. When I copy out of Access now and try to paste into EXCEL it of course paste's it across not up and down like I need it to


A:Data from Access table to Excel Form

Once it's in XL, you could try re-copy ; then (in a free area), Edit -- Paste Special -- Transpose -- OK.

Relevancy 70.52%

I have a form called GET ORDERS that requires input( i.e. Customer #, product, qty...etc) The form has a field called TOTAL PRICE. In the properties for TOTAL PRICE, on the DATA tab, I have the following calculation in the control source field
Now that expression works fine and displays the correct total price. However, I'm looking for a way to commit that value to my ORDERS table, in the TOTAL_PRICE field.
I'm assuming that I can do this under PROPERTIES and the Event tab..... I'm looking for someone with a little more Access experience to provide some help. Thanks in advance for your time.....

A:MS Access: Enter data into a table from a form.....

Hey. Will this help?

That'd be cool, 'cause I found it pretty quickly.
Relevancy 70.52%


I have a database with 10 different tables for 10 different classes. I want to create one table that has the information for all 10 classes. The fields are the same (first, last name, etc.) Do I use query? Import data? Simple question, but not so simple answer for me..

I'm a newbie...please use small words and speak slowly..

Relevancy 70.52%

- Moved question to this forum from Development Forum Workstations XP Pro systems Server Win Currently using MSAccess k database for client listing and associated notes etc one-to-many tbl client to tbl notes We recently purchased a software package and I believe I read in the literature that it is built on sybase platform In talking to the company about migrating data one of their techs stated that they Export access tables to csv comma del format bring the new systems table open in foxpro append the data from the csv files He said that foxpro accepts the tables better and keeps the table in a correct format for the commercial program to read use them So I contacted a friend who had Visual FoxPro on his laptop and he let me use the laptop for a short period of time I exported a few of the MSaccess tables INTO table Access tables foxpro data to csv format and then opened FoxPro BIG SURPRISE Never used or even seen screen shots of FoxPro so when it opened Access table data INTO foxpro tables up I guess I was expecting something quot similar quot to access or whatever but that was not the case Foxpro uses a command window and well Im lost I opened one of the commercial programs tables in foxpro and it took me a few minutes to look at the data currently in their table figured out it was some quot browse quot command but absolutely - totally different than what I was expecting QUESTION How do I append data to existing tables from csv files in FoxPro QUESTION Is there some down and dirty tutorials on FoxPro that I can read through Im not too proud to ask for some help here I pride myself in being able to figure out new different software packages but this has got me stumped Everything I type in google about foxpro takes me to msdn and a bunch of specific VB problems or something Once I get the raw data into the commercial programs tables then I can link them into the clients listing I just need help getting started Thanks in advance Semper Fi JR nbsp

Relevancy 70.52%

I am using access at my work that was created before I started here The table I am using was created a long time ago and has records Since the table was created there has been a lot more information that would be useful to have in our customer database Ultimately what I would like to do is create a new table and move all the information from one table into the new table because one of the necessary new Access table from 2003, one to data another Moving fields would be Customer ID using auto number Although I Access 2003, Moving data from one table to another realize Access wont let me assign that field in the current table as it already has data So with out much knowledge of the Access 2003, Moving data from one table to another program it seems to me that I need to make a new table with all the new fields and then move the data from the old table to the new table I have been searching for answers to this delemia and cant find any one who has asked this question Thank you Lindsey nbsp

A:Access 2003, Moving data from one table to another

Lindsey, welcome to the forum.
Add the field to your current table and make it an Autonumber.
It will do it.
What it won't do is take a current number field and change it to an Autonumber if there are any values in it.
Relevancy 69.66%

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.

Relevancy 69.66%

I have an old database with customer info in it. I am working with a new database however, that has some of the old customers in it. The old database has some customer details about these customers that I would like to have in my new database. I am having trouble adding the data from the table in the old database to the table in the new database.

So far I have imported the old table into my new database and have tried to Insert Into the New Customers table the CustDescriptions FROM the Old Customers table WHERE FirstName=FirstName And LastName=LastName And Address=Address. This did not work it just added new rows to my table instead of inserting the data into the current rows where the criteria matched.

Any help on how to make this work would be great! Thanks ahead of time.


A:Access 2000 - adding additional data to a table

Hi - firstly are you using different or the same versions of Access? Secondly I would just cut/copy and paste the records from table to table but you must ensure that the properties for the fields are identical.

Relevancy 69.66%

Recently my office upgraded our server to Windows Server Pro amp all the workstations to Pro as well We are all running Access with which we utilize tables and corresponding reports With the old system we could enter Data Table 2000 Reports For Saved Access - data in any table save that table preview the corresponding report print that report amp the new data would be included in the report With the new system it seems like Access has to be closed after saving data in order for the report to include the new data because when we print a report directly after saving we get the old data When we close out the app then reopen the app amp then preview amp print the reports the new data is then correctly included This is really Access 2000 - Saved Table Data For Reports inconvenient for the nature of the work we do Is there any way to set up Access to work our old Access 2000 - Saved Table Data For Reports way to have the saved data immediately available for inclusion in the corresponding reports Thanks in advance mark man nbsp

Relevancy 69.66%

Workstations XP Pro systems Server Win Currently using MSAccess k database for client listing and associated notes etc one-to-many tbl client to tbl notes We recently purchased a software package and I believe I read in the literature that it is built on sybase platform In talking to the company about migrating data one of their techs stated that they Export access tables to csv comma del format bring the new systems table open in foxpro append the data from the csv files He said that foxpro accepts the tables better and keeps the table in a correct format for the commercial program to read use them So I contacted a friend who had Visual FoxPro on his laptop and he let me use the laptop for a short period of time I INTO table Solved: Access foxpro data tables exported a few of the MSaccess tables to csv format and then opened FoxPro BIG Solved: Access table data INTO foxpro tables SURPRISE Never used or even Solved: Access table data INTO foxpro tables seen screen shots of FoxPro so when it opened up I guess I was expecting something quot similar quot to access or whatever but that was not the case Foxpro uses a command window and well Im lost I opened one of the commercial programs tables in foxpro and it took me a few minutes to look at the data currently in their table figured out it was some quot browse quot command but absolutely - totally different than what I was expecting QUESTION How do I append data to existing tables from csv files in FoxPro QUESTION Is there some down and dirty tutorials on FoxPro that I can read through Im not too proud to ask for some help here I pride myself in being able to figure out new different software packages but this has got me stumped Everything I type in google about foxpro takes me to msdn and a bunch of specific VB problems or something Once I get the raw data into the commercial programs tables then I can link them into the clients listing I just need help getting started Thanks in advance Semper Fi JR nbsp

A:Solved: Access table data INTO foxpro tables

is this in the right forum, or should it be moved?
Relevancy 69.66%

Hallo I m writing an MS Access database will end to UD from Excel Access table data query update up being Access but is currently a UD query to update Access table from Excel data mdb which is to be updated by Append and Update queries and also exports various data UD query to update Access table from Excel data As far as possible I m trying to make everything one-click deals for the user The data source for the Append and Update queries is an Excel file that contains columns of data This file is sent back and forth from the user to an external company who fill in data on existing rows add new records and delete old ones If there are values in column A and nothing in the other columns this generally means this is a new record that needs appending to the database table I ve written the Append query and that s operating fine If there are values in the other columns this generally means this is an existing record with new info so the database table needs to be updated I figured I d add a macro to the OnClick event of a button on the switchboard which would first fire the Append query and then the Update query However I don t know what I m doing with the Update Query of the columns in the Excel sheet are relevant and I m UD query to update Access table from Excel data updating the Stays table in the database Excel gt Access Chassis gt FullChassis Final Distr Point gt Dealer Order Type gt OrderType Model Type gt ModelType Hold gt Hold Sold gt VehicleSold However The Dealer field in the Stays table is actually a lookup autonumber ID field It looks up the DealerID field from the DealersSuppliers table and displays a text field called VCode So actually the values in the Final Distr Point in the Excel file equate to the values in the VCode field in the Dealers table Similarly the ModelType field in the Stays table is actually lookup to the autonumber ModelID field in the Models table and displays a text field called Model this is a simple field for the user There is also another text field in this table VModelCode and this is what the Model Type field in the Excel file actually equates to Finally the Hold and Sold fields in the Excel file either contain quot Yes quot or blank The Hold and VehicleSold fields in the databases are Yes No checkboxes So how do I write an update query that will update ID fields in the Stays table Do I need to do something with adding a WHERE condition in the query And how do I convert those quot Yes quot values to quot quot s so the checboxes get ticked Gram nbsp

Relevancy 69.66%

I created an access db amp populated a table using get external data from excel Now I need to import more records from excel to that same table I tried the same process but it insisted on creating a new table Repeat x get more new tables Even though when using get external data there is an option to import into an existing table the option is greyed out I need to get the new excel data into the already-populated table Speaking in human terms not Microsoftese how to I either merge the data from newly-created newly-populated and unwanted table into the existing table or import from excel into the existing table I realize that I could export from access back into excel merge the data there and import into a new table but surely there s a better way Or not since we re talking about one of the Big Flopper s biggest flops Access Of course the best solution would be to import it into an SQL db but that s not an option in this case office politics note to self CHILL nbsp

Relevancy 69.66%

Okay I ve got an Access database For this particular task I have a form with comboboxes and a textbox all four unbound The data on the comboboxes are populated RowSource from three separate queries which data Solved: Add 2003: form Access from table to run fine I have a button which when clicked I want to take the information and add it to a table tData My SQL is very rusty and I can t get it working right Here is the SQL statement Code INSERT INTO tData CowType CowLocation CowMovement User Date CowValue SELECT Forms fData CowType AS Expr Forms fData CowLocation Solved: Access 2003: Add data to table from form AS Expr Forms fData CowMovement AS Expr CurrentUser AS Expr Date Time AS Expr Forms fData CowValue AS Expr FROM tData As you can see the form fData should take the controls and add the value of each to the respective field in the table tData It s not working though and I m getting the retarded quot Invalid SQL statement expected DELETE INSERT PROCEDURE SELECT or UPDATE quot Can any Access SQL guru tell me what I m doing wrong Driving me crazy here nbsp

A:Solved: Access 2003: Add data to table from form

Nevermind, I got it. Went away from the Append query I was trying and did it via VBA...
Dim rsData As ADODB.Recordset
'Create data set for entry into table
Set rsData = New ADODB.Recordset
rsData.Open "SELECT * FROM [tData];", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rsData.Supports(adAddNew) Then
With rsData
.Fields("CowType") = Me.CowType.Value
.Fields("CowLocation") = Me.CowLocation.Value
.Fields("CowMovement") = Me.CowMovement.Value
.Fields("User") = CurrentUser()
.Fields("Date") = Date + Time()
.Fields("CowValue") = Me.CowValue.Value
End With
End If
Set rsData = Nothing
Relevancy 69.66%

Daily I receive an html email that contains multiple tables in the email
There are two table's whose data I want to import from that email. The only development tool I have is VBA

Importing the data is not the problem. The problem is that the resulting table is in a format that I can't use. I want every record to contain a name, but at the moment there will be a name and five empty fields beneath it for the same column. I want to fill those empty fields with the name above. I'd also like to fill in the same date for all records of a corresponding day for which I'm doing the import. I will be importing the data daily

Can I do that with VBA?

Relevancy 69.66%

My DB has a table Info with fields :Index and Data. A form has a series of textboxes 1 ....x. and a Command button which I want to use to automatically transfer data from table index 1 data to form Text1, index 2 to text2..etc.
I have spent hours trying to do this using For/Next loops and many other methods, none of which worked.
My Latest attemt was
DoCmd.OpenTable "Info"
DoCmd.GoToRecord acTable, "Info", acFirst
Text1 = acFirst
This is returning the second record index. Text1 =2.
Copy of table attached. What I want is F1 data, not interested in IDX apart from providing Text box ID.. If I can get the right Data I am thinking of a For/Next loop to populate the series of textboxes. Text Box no = IDX
IDX 1 2 3 4 5 6 7 8 9 10 11 12
F1 449 8 20 34 15 31 516 795 161 8 1 1
Can anyone help or am I trying the impossible?

Relevancy 69.23%

Hello -

I have a data table that is recording inspection dates and inspection purposes for specific records. There are a total of six occurences (i.e. Inspetion Purpose (1), Inspection Date (1), Inspection Purpose (2), Inspection Date (2)).I would like to use a form to input data into the fields. Is it possible to build an expression so that the data input into the form inserts the data into the table with the first available null value for that record.

In other words, if there is a value present in Inspection Purpose (1) & Inspection Date (1) then the data from the form is inserted into the fields for Inspection Purpose (2) & Inspection Date (2).

I would greatly appreciate any help!

Relevancy 69.23%

I am working on an Access DB written by someone much cleverer than me who has use a lot of SQL which I find hard to interpret It is a DB of client Excel table 2007 with existing not will Access data overwrite - info and order info I exported the Client Table to Excel so that I could more easily sort out the data separate name fields into Title First Name Last Name for example and then I wanted to import the cleaned data back into the Access DB I renamed the existing table as Client Original and I successfully imported the new table giving it the samename as the original used to have so that there wouldn t be any issues involving queries etc which relied on it unbeknown to me I needed to add a field from the Client Table to an Order Form but - to my surprise - the Access 2007 - will not overwrite existing table with Excel data only table available to me was the first one Access 2007 - will not overwrite existing table with Excel data which I had renamed as Original the new one which had the old name wasn t seen by the Add Fields box - it is as Access 2007 - will not overwrite existing table with Excel data if it doesn t exist but it does I then tried to import into the Client Original table instead but get the message that it cannot overwrite this table when I get to the end of the wizard There are no relationships at all involving the Client table and when I show the Client Original table on the relationships screen it s name is given with at the end of it which isn t in the name I gave it I am thinking that the guy who wrote the DB has something hidden in the SQL somewhere which is stopping me from being able to overwrite this table Has anyone got any ideas please Many thanks JHJ nbsp

Relevancy 69.23%

Hi all Not a huge tech head so tread lightly with me I ve recently designed and have been using a database for tracking of research participants I have my backend and frontend separate as per my husband data from no reading Access longer Solved: table Form s instructions I have buttons on my switchboard One for enter new participant That form can enter new data and doesn t show existing Solved: Access Form no longer reading data from table data My other button form is quot edit existing participant quot - i had to make some changes to it yesterday - add new fields move stuff around etc Solved: Access Form no longer reading data from table etc was having probs as computer kept shutting down etc However all going well but end of day open it up and the edit participant form no longer shows any existing participants data It s all still there in the backend but wont show up on the form My data entry Solved: Access Form no longer reading data from table is set to No - so that s not the problem The data record source is correct and all tables have been linked correctly between back and front ends Help please - this is work i ve been doing casually for my old organisation and they desperately need this and I ve mucked it up Aaaagh Thank you Carla nbsp

Relevancy 69.23%

I have created a select query where I am attempting to update/revise data to the Table in the datasheet view. This process seems to work only when running the query is first run. When changing criteria of the query and re-running the query, the fields are locked and I can not make any changes. Why is this? Is there a better way to make Table updates rather than a select query?

Relevancy 69.23%

Hello I hope someone can help. I am using Excel and Access 2000. I am trying to create a Pivot Table in Excel with a union query I created in Access. The union query works fine in Access and is pulling data straight from some text files (no calculations or anything). When I try to pull the data in from the access query I get the error "Too few parameters. Expected 11". I only have 10 columns of information that I am trying to pull over. I have spent all day trying to figure this out to no avail. We have done this before with other files in Access and it has worked before, but for some reason this one will not.

Any help would be appreciated.

Relevancy 68.37%

I created a new database that contains several different tables with relationships auto number fields and all the fun stuff that goes with those things However my workplace had been inputting data into an extremely large table for years This large table contains all the data that I need to be in my new database I was wondering if there is a way to take the contents of the one large table and append it to the multiple tables that I created in the new database To further complicate matters I changed some of the formatting and or names of fields Any thoughts or suggestions for the most effective and efficient method to complete one smaller large into table from Solved: in several Access tables data Appending this process would be greatly appreciated Just a note I have this week and next week to complete the design of the database and get all these people input into the database a daunting task I know So manually adding the data is not really a feasible option nbsp

A:Solved: Appending data from one large table into several smaller tables in Access
Relevancy 68.37%

Now that I have the rate information pulling from my other table, the Rate Total won't calculate unless I overtype the rate field with a new entry. What I would like to do is just press enter to confirm the field and move past it and have the total automatically recalculate.

I think that this would be an event procedure on Enter but I don't know what VBA code to use to make it happen?

Can you please tell me if this is correct and what code will make this recalculate?

A:Solved: Access 2007, Recalculate field after data is pulled from table. OBP??
Relevancy 67.51%

Good afternoon all I am working on writing a Security Incident Report program and I am running into a problem I have a Date Time field which I 2007 on based data field Creating Access table Auto-populate different Yes/No am attempting to auto-update from another Date Time field based on the result of a Yes No field input Is this even possible To be specific the user enters the date of the report On the next form page it asks the user if the report date is the same as the incident date If the user answers Yes I would like it to enter the data Creating Access 2007 Auto-populate field based on different table Yes/No data from the report date field and grey out the option to enter the new date If the report date is different from the incident date the user would then be required to enter the date of the incident This takes place three different times on the same form based on the incident date date responders cleared from the incident and the date the incident was placed under control I am running Access Thank you nbsp

A:Creating Access 2007 Auto-populate field based on different table Yes/No data

GrahamTechnology, welcome to the Forum.
Yes it is using VBA.
Relevancy 67.51%

Sorry for the long question post but need some help as I existing data w/o Access table records into overwriting 2010 import/append ve been searching for several days but havent found anything that helps I have table in my Access database that has exising Access 2010 import/append data into table w/o overwriting existing records records I have another table that after I run a query it first deletes the data in table then imports the GAL from the exchange server I run the import Access 2010 import/append data into table w/o overwriting existing records into table on a semi regular basis but have yet to copy all those records into table successfully I need to copy only the records from table to table if the records don t already exist in table I d like to use the email field as a foreign key to ensure there are only new records to be added to table Table primary key is ID autonumber So each time the query or vba code would run it would be continuing to grow table without duplicating existing data The data that I m trying to pull into table from table is lname fname phone email I have a small start of SQL but cannot get it to work properly because I m not sure how to add the other fields into this SQL statement properly unfortunately I don t know a whole lot about SQL or creating an append query so any assist is greatly appreciated INSERT INTO Current FirstName SELECT DISTINCT tblglobaladdresslistimport First FROM tblglobaladdresslistimport LEFT JOIN Current ON tblglobaladdresslistimport First Current FirstName WHERE Current FirstName Is Null nbsp
Relevancy 67.08%


While importing the essbase retrieved data from excel spreadsheet to access database using the folling Access VBA command, I get 65 K of blank rows into the table. This only happens for essbase retrieved data, other excel files works just fine. Is there a reason, and how can I prevent this 65 K blank rows? Please help. Thank you much.

VBA command to import excel data

DoCmd.TransferSpreadsheet acImport, nSpreadSheetType, "SPECTRUM_CALCULATION", _
cFileName, Me!ChkBox_FieldNames


A:import essbase retrieved excel data to access table adds 65 K blank rows

essbase is probably using empty strings, which Access picks up as data. Instead of importing to a new table, set up your table with a primary key, one that corresponds to the Excel column most likely to have data in it every time. Set allow empty strings to no on that field as well. That should import only the "filled" records.
Relevancy 64.93%

I have different mailing lists that I want to add to a mailing list in my database. There may be duplicate addresses in these lists. I want to only add an Address, FirstName, LastName record once. So how do I combine these lists with my original list without adding a duplicate Address, FirstName, LastName record?

A:importing data from a table to another table without adding duplicates?
Relevancy 64.93%

I have a [COLOR=green !important][COLOR=green !important]database[/COLOR][/COLOR] with 1 table named "Demographic" and 1 table named "Client data"; Client data is linked to an Excel spreadsheet. Both tables have different headings/fields for each column but similar data. I would like to get the data from the Client Data table into the Demographic table and correct for the mismatched column headings so that Client Data is correct in Demographic.

Would someone be willing to help me step-by-step so that I can merge these 2 tables AND have the data correctly aligned in the Demographic table?

I'm essentially a newbie...please be gentle with me.


Relevancy 64.07%

Hi there,

I have excel 2007 and I am trying to delete a table I put in. (Insert > Table) The problem is when i do this it deletes the data within the table. I dont want to have to delete the table and put in all the information again. Is there an easy way to do this? I looked at a similar post and it said to press the filter button on the data tab. This does nothing.

Any help or suggestions would be greatly aprreciated.


Relevancy 61.92%

I have a Make Table query that when I run it and view the table in the query it shows only the data I want. But when I go and open the table that theis query made, there are entries that should not be there.

It is putting in rows with blank fields, I am not sure how to stop it. I have tired to put something in the query for that field but I get back the error "Invalid Use of Null"

If I could put in the criteria that worked I would be all set.

Any ideas?

A:Solved: Access (Make Table) Query Results Returned not Same as Table
Relevancy 61.92%

I'm using Excel 2007, Windows vista. I need to compare actual costs (sheet 1) and estimated costs (sheet 2) to get the cost variance (sheet three) I'm getting really wacked out numbers using consolidation, but I have no idea what I'm doing

A:excel - comparing two worksheets to get data for a third

May I assume that the rows in Sheet1 and Sheet2 are not the same?
There must bbe a common denominator against which you want / can compare the costs?
A simple sample would help give the picture of the actual situation.
Relevancy 61.92%


I have the following challenge: I need to compare two excel files and check if certain identification numbers of participants in the one file are also present in the second file. The first file contains the identification numbers of the people who were invited to complete a questionnaire (SampleMapLookUp) and the second file contains the data of the questionnaires that were sent back (SampleFileBase). Is there a way to automate this process? In annex two sample files: SampleMapLookUp is the file with the numbers (name of the column is "ID_IND") that I want to check in the second file: SampleMapBase (name of the column here is "his_1").
I don't know if it was necessary but for testing sake I marked the numbers that should be found in yellow in the SampleMapBase. The resulting file should ideally have the lay-out and content of SampleMapLookUp.

Thanks for any advice,


Relevancy 61.92%


Just wondering if anyone could tell me if and how i can revert back to an older version of a table.

I had a table saved contain product details
whilst i was away a work mate accessed the table deleted some colums and saved over the table.
i was just wondering if the was anyway to undo this save, or if a back of the previous table could be found somewhere.

using Access 2000 on Win XP (no restore point available).

thx in adv.

A:Access 2000 table backups? / undo a save to a table

Not unless it's on a drive where you have been backing up the entire database or have specifically set up for the database to backup the data...
Relevancy 61.49%

I have two access tables I would like to merge data into one table. How to do?

I tried blank database importing one by one but result is I get two tables not one.

A:Importing data from one table into another table

You are going to have to write an insert query. If you view it in SQL mode it would look something like:

FROM MyTable2;
Relevancy 61.49%

I am having two column with current month profit & last month profit respectively Column A & Column B. I would like to compare both column & set the ICON (down Arrow & up Arrow ) based on result if A1 value greater than B1 set upper arrow else down arrow

A:How to set ICON in Excel by comparing data in 2 Columns

You didn't say where you wanted the arrows to appear, so I'll use C1 for this explanation.1 - In C1, enter this formula: =IF(A1>B1,2,IF(A1=B1,1,0))2 - Under the Conditional Formatting drop down, choose Icon Sets and then choose the Up arrow.3 - Go back into Conditional Formatting and choose Manage Rules4 - Edit the Rule for C1 as follows:----- Click the Show Icon Only box to place checkmark in box----- Change Type to be Number in both dropdowns----- Up Arrow: when value is >=2----- Sideways Arrow: when <2 and >0----- Down Arrow: when <=0 (should be default)5 - Click OKLet me know how that works for you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 61.49%

Hi All,

i want to compare data between two work sheets of one workbook and if data matches then it will be copied to third work sheet automatically. can some body help me.


Relevancy 61.49%


First, thank you all for you time and knowledge in advance.

Here's my situation. I'm working with file that is sent to me with populated information, in this example Col A
What I would like to do is enter information in Col B and then do a comparison of both A and B, letting me know if something in Col B is not in Col A and also is something in Col A not in Col B.

I need to know if what was sent to my in the spread sheet match what I physically inventoried.

I have provided an example.

Hopefully I did not over complicate my request.

Thank you,


A:Solved: comparing data in two columns in Excel

does this help

or this may ble closer to what you need
Relevancy 61.49%

hi guys, I have different data sets from two different software programs these two programs share data.these are bookkeeping entries. debet creditbank 100bank1 200.18soft 100soft 200.18the first data set consist of the last two entries of soft but then like a a lot of entries of soft. the second data set consist als of soft debet creditsoft 300.18person1 100person2 200.18i have to match soft of the first data set that consist of soft 200.18 and 100 with the second data set dat consist of the same soft but the sum of the individuals soft.can sombody give me an idea on how to look at these problems in excel.

A:excel challenge comparing two data sets

We probably have a language issue here, so it may a few tries before we can offer a solution. At this point I am totally confused by your request. Perhaps you could post some more examples of your input along with the desired output. We want to help, really we do!Please click on the following line and read the instructions on how to post example data in this forum. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 61.06%

I am starting this Access database from scratch. I have to import/upload a sales file weekly. I will be adding this file to a main sales data table. The issue I have is that this file does not have any dates on it. I need to be able to add a start and end date to the table.

Example fields that I would need/have

start date
end date

Item #
Store #
Sales Units
Sales Dollars

What is the best way to handle this? I have an idea - just not sure what order I should put my steps for importing the file, asking for the date range, adding the date to the table, etc....

Relevancy 61.06%

I'm trying to run a clear table query (to keep the table, but delete the data), and then an append query to repopulate the table, and it's not working.

...any idea what I'm doing wrong?

Relevancy 61.06%

I am having trouble updating data from one table to another. I am not Appending the data.

I've linked the two tables on an ID field.
I created a select query to see the data I would update.
I changed the query to an Update query.
In the Update To field I placed the following: [table].[field name]

After running the query I noticed the update did not work. The data from the first Table did not get updated to the second Table it wound up being deleted from the first Table.

Your help is appreciated.


A:Solved: Access 2003 Update Table from another Table

Buttercup1, welcome to the Forum.
I am not sure why you wan to update the second table with data from the first table.
Can you show me a screenshot of your query with the table(s) in and also your Table relationships?
Relevancy 61.06%

Hey there folks...
I am in need of some assistance. Obviously!

I need a form which allows a user to select a record from one table and completely move the record to a totally separate table in the same database. I need it to transfer all the data for the selected record.

AutoNumber will not be an issue as each record has unique identifiers.

I found this article online but it seems it will append ALL data and doesn't allow a user to select the correct record. This uses an Append Query which makes sense but again it seems as though it will transfer ALL records. All I need is for it to transfer a specific record depending on the user's selection in the form.

A:MS Access 2007: Moving a record from one table to another table...

Is this to "Archive" the record?
It is not normally necessary to actually "Move" a record, as you can just have a tick box called archive or similar that can be used to exclude that record from normal display and to display it as an Archived record.
There are 2 ways to do what you want, if it is really necessary, one is using an Append query, you can Identify the currently selected record on the Form using
forms![Form Name]![name of Key Field]
in the First Criteria row of your Key field, where Form name is the actual name of your form and name of Key Field is the actual name of your Key Field.
The other version is to use a VBA recordset Clone to do the same thing.
Relevancy 61.06%

In this database, i have a data entry form called "Information Requested" and all information in this form when entered are added into table Information Requested. In the form, 3 fields Names, Business Units and Emails are drop down lists based on table Contact List. However, what the form does now is i have to pick the name, business unit and email manually. It's not really efficient this way. what i want is when i pick the name, the corresponding business unit and email of that person will automatically show up in the box. How can I achieve this?

A:[Access 2010] How to append record from one table to another table.

The data should NOT be transferred to the table, only displayed on the Form, as it should only be entered once.
You can display it on the form by including the data in the Combo as a "Concatenated" field or you can transfer the data to "Unbound" fields.
Relevancy 60.63%

Hi Hopefully one of you delightful people can help Comparing/Matching Excel & worksheets more in across Solved: data me with a small problem I m having pulling together data from multiple reports I have manually combined separate reports Solved: Comparing/Matching data across in Excel worksheets & more into workbook with worksheets for each report Each row represents a separate job The st worksheet is an export from our own Access database the others come from external reports Whilst each report worksheet contains different sets of data column in each worksheet contains amp CSR amp or amp Site ID amp numbers These numbers can be used to match the jobs - however a not every worksheet has every number b some numbers may appear more than once on the same worksheet these are essentially returns to the same job The st Solved: Comparing/Matching data across in Excel worksheets & more worksheet also has a column with OPID numbers Column A these are unique numbers that we have paired to a CSR or Site ID number I want to compare the numbers in the CSR column of the st worksheet Column B of Sheet with the CSR columns in the other worksheets and where there is a match copy add the corresponding OPID number to a blank column in each worksheet What would be the best way to do this Thanks in advance for any help and advice offered nbsp

Relevancy 57.62%

I am trying to perform a query on two tables that will only return the date when the date field in both tables is the same. When I look at the two individual tables I can see the date field is the same in both but whether I do a JOIN or WHERE the querry is not returning any data. I have checked to verify that the date field in both tables are defined the same way. Any ideas what I am doing wrong here? Thanks

Relevancy 56.76%

I ve tried searching in this forum but I either didn t find any or I m using the wrong keywords What I m trying to do is 2007 Dates from Tables Comparing Access different get Access 2007 Comparing Dates from different Tables information from Table with information from Table but the criteria is that they if the e g names match change the address in Table only if the date in table is more current than the date in table As far as I can tell from the help files they only provide comparisons between a given date DD MM YYYY which means I can extract thru a query dates before after between a given date E g Dashes just makes the table easier to read Table Name Address Date Aa-- ---- - - Ab-- ---- - - Table Name Address Date Aa-- ---- - - Ab-- ---- - - Query -- gt Table Name Address Date Aa-- ---- - - This Big database contains over mil and the dates range from to end of And I have about smaller databases to compare and update with If anyone can point me in the right direction it would be very much appreciated nbsp

Relevancy 56.33%

YSB I really tried to figure this one out on my own I have looked through the indexes of two books searched through my Access links even did a search on a Access UBB and still come up with nothing It seems like it would be a common enough control I am really surprised that the solution is eluding me Ok with that out of the way How do I compare two Access and two greater the selecting (Solved) 97 Comparing fields in fields and pick the greater of the two values Form rate rate and unboundfield The unboundfield s control source should select whichever value is greater The IIF function is not covered very well (Solved) Comparing two fields and selecting the greater in Access 97 in my (Solved) Comparing two fields and selecting the greater in Access 97 Mastering Access book so I am not sure if that is what I need or not It seems similar to the conditional control iif total gt total EXTRate that I have tried to convert that one to work ------------------ Building the Ultimate site list for PC support This message has been edited by Talismanic edited - - nbsp

A:(Solved) Comparing two fields and selecting the greater in Access 97


This seems to work =IIf([rate1]>[rate2],[rate1],[Rate2])

Thanks anyways!

Building the Ultimate site list for PC support.
Relevancy 55.47%

Problem summary: I want to make a table (- 'NEW TABLE' -called 'By Country:% of People Whose Total Training Hours are in 5 different bands') using the data in a pivot table. I want my 'NEW TABLE' to analyse the data by country. How do I refer to a pivot table cell in my NEW TABLE'S formula, when the pivot table data keeps shifting cells depending on the country I filter for?

Problem detail:
My pivot table has each person's training hours for multiple courses totalled, & it allows me to filter for different countries in turn.

Dreamboat has already kindly told me the formulas for my 'NEW TABLE' to get the % no. of people whose total training hours are between 0 - 8 hours, 9-16 hours, 17-24 hours, 25-32 hours, 33-40 hours.

Sorry this sounds a bit involved.

Thanks for consideration of my problem!


A:Use pivot table data in formula outside pivot table

Jen: Didn't I teach you not to use pivot tables yet?

I suck at them.
Relevancy 55.47%

I have an Access 2003 table (Table1) with four fields:
Amount (number, single format, 3 decimal places)
Add (number, single format, 3 decimal places)
Calc (number, single format, 3 decimal places)
Test (number, single format, 3 decimal places)

There is one record:

The first query updates the "Test" field by [Amount]*[Add] with the result that the test field now contains the value 40.84

Then I run a query to find records where [Test]<>[Calc] and it returns the record even though the values in the two fields are equal.

Help! Please???

A:Solved: problem with access 2003 calculation comparing values

What does the record say when you run the 2nd query?

When I run the second query, I get a 0 for the first record (using the info you listed). I added in a few records of my own and got -1. If I'm not mistaken the 0 means False/No and the -1 means True/Yes. So the 0 means that no records match [Test]<>[Calc] and the -1 means that records match [Test]<>[Calc].

Either way please explain more so I can get a better handle on what you are looking for.

Relevancy 54.18%


I am using Access and one of the code im using is for transfering data from a form to another form with the following code:

Private Sub Command5_Click()
[Forms]![FAA PTRS ENTRY form]![InspectorName] = [Forms]![codeF]![DEPT]
[Forms]![FAA PTRS ENTRY form]![GACARPTRSREFNO] = [Forms]![codeF]!


DoCmd.Close acForm, "codef", acSaveNo
End Sub

it works fine, but when done , it seemed to duplicate the data in the table and now i have two same code in the same table, even though the message say acSaveNo. How can i prevent it to create a copy in the table everytime i chose a code.



Relevancy 54.18%

Hi Guys

Hi am trying to duplicate the entire content of a table into the same table but adding the prefix "A_" in front of the new data. With this i will have the same data twice but one with the "A-" all in the same table.

Can any body help me?


Relevancy 54.18%

Hi and thanks table of data a Building for any help I am a newbie to VBA programming I am creating a worksheet of contacts and the Building a table of data date and time that I phone them along with their current quot status quot in our company I have created a form and have got it o do the insert of new data that I input BUT What I would really like is enter a quot client number quot which is unique and have it search my table for any record s with that client number and the show me the last record that matches the client number this can all be done from within my form then If I click on the quot insert quot command button I want it to insert a new row after the current row and move my form data in the form into the new row which will include a date and time So To summarize LOOKUP ----- I need to have my form scan down the data for a match in a particular pre-sorted column stopping at the last line that matches then display the data or dispay nothing is there is no match INSERT ----- I need to have the form scan down the data for a match in a particular pre-sorted column stopping at the last line that matchesinsert a blank row here and move my data into it Rob nbsp
Relevancy 54.18%


I need to add rows from Access table to Excel spreadsheet by using VBA.

I cannot create a new file for various reasons, so I cannot use command “TransferSpreadsheet”.

Is it possible to do it?


A:Solved: Access 2003. How to add rows from Access table to Excel spreadsheet by using
Relevancy 54.18%


We have an Access application that holds employees' table. When new employee comes, the email stating the name of a new employee and the link to this Access app. being sent to a supervisor. When supervisor starts the app, it opens the list of all new employees for a supervisor to choose from.
What my boss wants is, that access will open a new employee record by itself, depending on the name from the email. In other words, he wants to open the specific record by the link from email.

Is it possible to do this?


A:Solved: Access 2003 - Opening specific record in Access table via link in Outlook
Relevancy 53.32%

I m looking for some help from any of you Excel geniuses out there I have attached a sample spreadsheet sensitive data removed which shows when data Excel Pulling in table a from a client contacted the service and when and what security items they had installed What I m looking to do by way of a formula is take data out of the Referrals sheet and display it in the Equipment sheet I would like the formula to look at C for each month and return a value for the number of Window Alarms installed in October I have previously used SUMPRODUCT to Pulling data from a table in Excel pull info and display it in another sheet but i m not sure how to do this I ve tried to use SUMPRODUCT and DATEVALUE to look between for dates in C gt and lt and then return a number based on values that another criteria in other cell Can I use SUMPRODUCT or do I need another formula Pulling data from a table in Excel Please ask any questions as i m sure I have confused you with my question Thanks in advance Chris nbsp

Relevancy 53.32%

Ok I have created a database, split the database, and provided multiple users with the Front End copy of the data. Everyone is keying in the form and adding records to the table.

I have a little problem.

One user will key in the bulk of the information but another user will need to finish the record. I have a "Find" command button on the form for them to be able to look up a record number but the problem is:

The user that needs to finish the record cant see what the inital keyer has keyed until she goes all the way out of the database and back in.

Is there a refresh button somewhere that will allow all the users to see all that data that has been keyed in at that point? I hope thats clear enough.....

(Access 2003 )

A:Updating data within a form/table......

Figured it out . For the readers all I had to do was add "Me.Requery" to the code of the Find Record command and it updated the database before they actually would look for the records.
Relevancy 53.32%

How can one tell the date/time when an Ms Access Table was last updated? I know you can display the Modified and Created Date of the table object in the Nav Pane however I believe those dates refer to the object itself and not the data within the object.

A:MsAccess Table data Last updated

I don't think you can, unless you arrange to store it yourself.
Relevancy 53.32%

I have a series of tables representing the same stadium seat layout so some rows have more cells seats than others One cell in a table has an instruction for one seat e g a color for stunt n of a card show with the next table being the next not but summarizing Rearranging data table instruction stunt n I want another tab to rearrange my input Each corresponding cell seat will be a row Then that cell s value in each table follows So if I choose to put each table in a new tab with the first seat being in A every time I want to track A s value through each table stunt for the show Can Rearranging but not summarizing table data pivot tables help me rearrange Right now I m using references like A to help but consecutive seats are columns in the tables whereas my new output will have one seat per row So to summarize I have tables showing the same layout over different points in time and I want to see each corresponding cell s value through Rearranging but not summarizing table data time
Relevancy 53.32%

Anyone know how to accumulate stats from Pivot Tables.

For example I have 2 pivot table,

table 1 :
Pens 10
Pencils 5
Erasers 4

table 2:
Pens 6
Erasers 3

I want to have another table that adds total Pens, Pencils and Erasers.
I also want the table to incorporate additional Pencils should tabel 2 acquire them.
And I also want the table not to fail of I suddenly lose the Pens from one of the tables.

Mant thanks
Relevancy 53.32%

I have an excel 2010 chart that shows a data table

The data table includes the 4% difference data I used in order to show a target range.

My question-- is there a way to hide that 4% data line in the data table without changing the graph?

A:Excel Data Table - hide a row?

I've always found the automatic tables somewhat inflexible. If it was a non automatic table you could hide the row and get excel to plot hidden rows. I don't know of a way to do the reverse.
You could also cheat I suppose and apply a shape to mask the row you don't want including. If you were to do that, I'd make the series you want to hide appear at the bottom of the table, so your column headers and the first series don't have a gap between them when you mask the hidden series. In the attached I did just that and selected / deleted the hidden range from the legend.
Relevancy 53.32%

okay, I have a person that uses a word document to enter data in to. Well she has been complaining that she has been saving every few minutes but yet the next day when she goes back, that data is gone. I thought it was user error, but then I hired a new guy that is pretty computer literate and he came in this morning and his data was gone from the day before. He did save the night before when he left. I have it set to autosave recovery info every 2 minutes and fast saves is disabled. Any ideas? the file they are accessing is located on a shared folder on another computer on the network. I'm lost.

Relevancy 53.32%

I have a MySQL table that has about 105,000 records.
CREATE TABLE IF NOT EXISTS `tbl_message_log` (
`pk_record_id` int(11) NOT NULL AUTO_INCREMENT,
`fk_user_id` int(11) DEFAULT NULL,
`message` varchar(640) DEFAULT NULL,
`message_recipient` varchar(25) DEFAULT NULL,
PRIMARY KEY (`pk_record_id`)
I want to replace all the records of the column "message" with a customer text 'This is a test mess'+the 'fk_user_id' in this case the 100th record will be filled with a text "This is a test mess 100"

How can I do it using SQL commands in PHPmyadmin?

A:Replace all MySQL data in a table?

I think the easiest approach will be to export the table records to Excel / CSV file and then use formulas to make your manipulations in Excel workbook itself. You can then either re-import the CSV file into the table or you use additional formulas in Excel to dynamically create a separate insert statement for each record which is not very ideal for this situation since you have more than 100k records. I'm sure this could also be done in pure SQL by re-writing the records to a second "temp" table but my SQL skills are mediocre at best and you'd need to use Google to get more info. The export / import option I mentioned first should be pretty straightforward. Don't forget to back everything up fully before trying to manipulate the records.
Relevancy 53.32%

Hi I have a Access database Form that opens returning Change data table containing data from one table and when Change data returning table clicking a button i wish it to transfer the forms text box information into another form for the reason being that i wish to delete the original information quot session quot table where all the current data is and make a copy of it into another table quot Session CLX quot table for it to be archived How do I change the current database in VBA code i hope you can help Colin a sample of my inheritted code is as below - ------------------------------------------------------- ------------------------------------------------------- Private Sub btnDelete Click Dim dbs As Database qdf As QueryDef rst As Recordset Return reference Change data returning table to current database Set dbs CurrentDb Create new query Set qdf dbs CreateQueryDef quot quot Construct SQL statement including parameters qdf SQL quot PARAMETERS simcode TEXT quot amp quot start date DATETIME end date DATETIME quot amp quot session type STRING customer code STRING quot amp quot checkee STRING checker STRING prog code STRING quot amp quot simconfig STRING simfmc STRING updated date STRING Instructor STRING quot amp quot INSERT into session last simulator code session start session type quot amp quot customer code session end quot amp quot checker name checkee name quot amp quot program code sim fmc sim config updated date priv fee sim motion type Instructor Case CLX Date CLX UserName CLX Authority CLX Reason values simcode start date quot amp quot session type customer code end date quot amp quot checker quot amp quot checkee prog code quot amp quot simfmc simconfig updated date priv fee sim motion type Instructor Case CLX Date CLX UserName CLX Authority CLX Reason quot qdf Parameters quot simcode quot simulatorCode qdf Parameters quot start date quot txtStartDate amp quot quot amp txtStartTime qdf Parameters quot end date quot txtEndDate amp quot quot amp txtEndTime qdf Parameters quot session type quot cboSessionType qdf Parameters quot customer code quot cboCustomerCode qdf Parameters quot checkee quot txtUsercreate qdf Parameters quot checker quot txtSpecialDetails qdf Parameters quot prog code quot cboProgramCode qdf Parameters quot simconfig quot cboSimConfig qdf Parameters quot simfmc quot cboFmc Load qdf Parameters quot Instructor quot cboInstructor qdf Parameters quot updated date quot txtcreatedate qdf Parameters quot priv fee quot txtPrivFee qdf Parameters quot sim motion type quot cboSimMotionType qdf Parameters quot Case quot cboCase qdf Parameters quot CLX UserName quot txtEmployee qdf Parameters quot CLX Date quot txtCLXDate qdf Parameters quot CLX Authority quot txtCLXAuthority qdf Parameters quot CLX Reason quot txtCLXReason Run select query and populate Debug Print quot about to execute the update quot qdf Execute Set dbs Nothing End Sub -------------------------------------------------------------------------- -------------------------------------------------------------------------- nbsp

A:Change data returning table

Can I ask why you are going down this route rather just having an "Archive" tick box in the Original Table, you can then Filter the Records to only show unticked records and only ticked records for archived data?
As you are using VBA why not do the update directly using Recordsets rather than SQL?
Relevancy 53.32%

Hi, Can someone help me with this problem. Please refer this spreadsheet. say for the table 1, C6 i choose Sam, how can it copy automatically data from table 2 to table 1 (D24:K24 to D6:K6)Thank you.message edited by nur11

A:What formula to copy data to table?

First, a posting tip:When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "I Need A Formula" we wouldn't be able to tell one question from another and the Archives would essentially be useless. I have edited your subject to be more relevant to your question.That said, try this:Put this in D6 and drag it over to K6 and as far down as you need.=INDEX($D$24:$K$35,MATCH($C6,$C$24:$C$35,0),COLUMN()-3)The MATCH function will determine which row of the lookup_array C24:C35 contains the value in C6. It will use that as the row_num argument for the INDEX function. COLUMN()-3 is the column_num argument for the INDEX function. By using the COLUMN() function as opposed to hard-coding the column number of the lookup_array, you can drag the formula over and the column_num argument will update itself.Let us know if you have any questions.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 53.32%

Looking for the best approach to updating altering an Access table via DSN-less connection string Situation This process will be run nightly via MS Schedule tool Database - contains table which acts as a linked central datasource for numerous databases - this cannot be change - in some instances this maybe left active which Access w/Oracle table Access Linked Using to update affects the altering of the table Alter is need to change fields s data types or convert prior to update Database - contains the temporary table and the code or possibly just the code to update the data in the linked table source Note this code cannot be kept in Using Access w/Oracle to update Access Linked table DATABASE - due to the fact that this database is occasionally opened and used by other users so I cannot have the Autoexec fired when the user access the database - hence the need for Database Now the Oracle data source contains numerous fields that need to be converted in way or another including converting a date that in contains a text varchar datatype ie when I attempt to covert this and place in an Access Date Time field - I am getting an error My thought is to create the temp table then export into Database - which will replace the existing with the new data - or possibly have the Database -tbl linked in Database and do a delete and append queries - however then I run into the conversion issue Any and all suggestions is greatly appreciated Note the date field SERVICE DUE DATE CMT LAST SERVICE DATE PURCHASE DATE that needs the conversion within the code or using the alter tables Thanks Karen Here is my existing code Public Function FTCSConnection Dim sConn As String Dim oConn As ADODB Connection Dim rstOra As ADODB Recordset rs As ADODB Recordset Dim adoRS As ADODB Recordset Dim cn As ADODB Connection Dim ctl As Control Dim j I As Long Dim rsField tblField As String Dim rsValue tblValue As String Dim varNM As Variant Nomenclature Modifier Dim varLSD As Variant Last Service Date Dim varRNG As Variant Range Dim varSDD As Variant Service Due Date Cmt Dim varEQL As Variant Equipment Location Dim varPAmt As Variant PURCHASE AMT Dim varPDate As Variant PURCHASE DATE On Error GoTo FTCSConnection Error DoCmd SetWarnings False Set cn CurrentProject Connection sConn quot Provider OraOLEDB Oracle Data Source DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP quot amp quot Host Server com Port CONNECT DATA SID serv quot amp quot User Id Password quot Set adoConn New ADODB Connection adoConn Open sConn Set adoRS New ADODB Recordset STRSQL quot Alter Table TL FTEM TEMP alter column SERVICE DUE DATE CMT varchar quot CurrentProject Connection Execute STRSQL STRSQL quot Alter Table TL FTEM TEMP alter column LAST SERVICE DATE varchar quot CurrentProject Connection Execute STRSQL STRSQL quot Delete from TL FTEM TEMP quot CurrentProject Connection Execute STRSQL Set rs New ADODB Recordset STRSQL quot Select from TL FTEM TEMP quot rs Open STRSQL cn adOpenDynamic adLockOptimistic STRSQL quot SELECT DISTINCT A FTEM ID AS FirstofEquipment ID A EQPT NAME AS NOMENCLATURE A NOMEN MODIFIER NAME AS Nomenclature Modifier quot amp quot A EQPT LOC NO A SERVICE ORGN CODE COUNT AS CountOfEQUIPMENT ID quot amp quot A SERVICE DUE DATE CMT A LAST SERVICE DATE A MFR NAME AS Manufacturer A MFR NO AS Model A PART VENDOR SERIAL NO AS VendorPart A RANGE quot amp quot EM PURCHASE AMT EM PURCHASE DATE quot amp quot FROM Server FTEM VI VW AS A INNER JOIN Server EQUIPMENT MANAGEMENT AS EM ON A FTEM ID EM FTEM ID quot amp quot GROUP BY A FTEM ID A EQPT NAME A NOMEN MODIFIER NAME A EQPT LOC NO A SERVICE ORGN CODE quot amp quot A Service Due Date CMT quot amp quot A LAST SERVICE DATE A MFR NAME A MFR NO A PART VENDOR SERIAL NO A RANGE quot amp quot EM PURCHASE AMT EM PURCHASE DATE quot amp quot HAVING A SERVICE ORGN CODE Is Not Null quot amp quot ORDER BY A FTEM ID quot Debug Print STRSQL Set adoRS New ADODB Recordset adoRS Open STRSQL adoConn adOpenDynamic adLockReadOnly adoRS MoveFirst Do Unti... Read more
Relevancy 52.89%

Hi, I hope someone can help me with this.Please refer this spreadsheet. table 1, at functional role column (F7), there is list of functional role that I have to choose. At table 2 (AX6:DS25), I put this formula for cell AX9 =IFERROR(INDEX($C$203:$BX$231, MATCH($F9,$B$203:$B$231,0),1),"") so that when I choose the respective functional role, it automatically copied data from table 3 to table 2. My question is, how can I combine formula so that I can choose another list (Specific position column (G7), so that the data from table 4 can be copied to table 2, based on the specific position i choose.Please note that the row header is the same for table 2, table 3 and table 4. I hope u guys can understand my question.\Thanks in advance.
Relevancy 52.89%


I required vba codings for pivot table but the value should be choose from the data validation i have created.

For example : I have two pivot tables in sheet1 and these two pivot table have a common column filed available so i have created the data validation in cell D6. Now i need to give reference to the cell D6 for both the pivot table on the column labels.

Based on the value selected on the drop down the pivot table need to be changed on the both pivot tables.



Relevancy 52.89%

I'm on Oracle 10g

Is it possible to use the data within a table to create another table? In other words, instead of using select column1 from table_1 into table_2, is it possible to use the row-contents of column1 to make table_2? I basically need to pivot a column into a new table (standard function in 11g).

A different way of asking: I have a table with a first_name column. I want to make a new table but instead of copying first_name from table_1, I want to have columns called JOHN, MARY, FRED, AUDREY.....ANDREW. I don't know what names are contained in the first_name column, and they will change all the time, so hardcoding isn't an option.

If you follow me.

A:Sql question - data driven table generation?

kmel, I do not know Oracle SQL, does it have the Pivot Table or Crosstab Query ability, I am not sure if it is a "Pivot" or not?
see this link
If it does you should then be able to create a Make Table Query based on the Crosstab query rather than another table.
I could do it Access
Relevancy 52.89%

My primary hard drive is seen (by Windows/apps) as being twice it's real size. Is there any way to resize it without losing all of the data I've loaded onto it?

Initially, I tried to stripe two drives, but gave up and removed the disk array. I installed a lot of software before I discovered that the boot disk partition table was still twice it's actual size. It's not causing a problem, but I suspect that somewhere down the road it will. I really don't want to start from scratch, if I can help it.
Relevancy 52.89%

Access 2003

Standard split database with a back end and front end. The form that is being used has one field that is not transfering its data to the table. Its a calculated field. Its called Premium Difference. Its taking one field and subtracting it with another and the total is populating in the Prem. Diff field within my form but its not saving to the table.

Can someone help. Its really probably something simple ..

Relevancy 52.89%

I am trying to 'track' the prices of computer parts on 2 different online stores, I would like to have excel display the product name , price , and total price of all in a table. Is this possible.

example product :

Corsair Memory Vengeance Low Profile Jet Black 8GB DDR3 PC3-12800 CAS 11 Dual Channel Desktop - CML8GX3M2B1600C11 -

A:How To Retreive Data From A Website And Display In Table

Relevancy 52.89%

Dear all,

I am attaching my DB.

The problem I'm facing is that in the Course_Details table, the first record in the Course_ID field keeps changing it's data without any reason.

Hence, it affects the combo box in the forms when I click on the Course details, course & cost report button on the switchboard.

Qns: What's the reason for the auto change of data in the table?

Thanks a lot!

Relevancy 52.89%

Hi guys,

is there a function i can use to do the following:

Sheet 1 is a massive table
Sheet 2, 3, 4 etc (changes as they are inserted)

i need a function i can put in sheets 2 onwards that looks up the sheets identifier in sheet 1 table and inserts data in the corresponding correct col

i know vlookup can 'pull' the data to the table
but with the changing nature of the consecutive sheets
i need to 'push' the data to the table

Please help

A:Excel07 function to insert data to table

can if-then-else be used to acheive this?? or something similar
Relevancy 52.89%

Hiyo I have an Excel file with worksheets The first is a planner data from 7-day populate Excel table data table which has records with the following fields Date Time Service Type Client Location The data is Excel populate 7-day planner from data table updated once a week on a Monday and is a plan for the week Excel populate 7-day planner from data table ahead I need to transpose the Service Type and Client probably something like Client amp Chr amp Type amp quot - quot amp Service values onto the nd sheet which is a day planner split into sections one for each day Say the planner looks like this simplified version with Date i e Monday of the current week in cell A Date Loc Loc Loc Loc AM AM AM AM etc Date Loc Loc Loc Loc AM AM AM AM etc Click to expand Loc - Excel populate 7-day planner from data table Loc are the Locations and Loc is an empty cell for entries in the table sheet that haven t got a location specified I need to perform some manner of lookup index-match something in each of the cells on the planner sheet to populate them with the data from the table sheet So for cell B to populate there would have to be a record in the table with - a Date equal to the Date value in cell A - a Time equal to the time in A AM - a Location equal to the Loc value in cell B For E to populate there would have to be a record in the table with - a Date equal to the Date value in cell A - a Time equal to the time in A AM - a Location of quot quot effectively equal to the Loc value in cell E blank Any ideas There s probably some sample day planner file out there somewhere that does exactly what I need but I haven t found it yet nbsp

Relevancy 52.89%

I've added tables to a database and these tables have autonumber as the key. When I want to clear this db and start again - how do I get the autonumber key to reset to 0 so that I can populate the table from scratch.

It's not working incorrectly, I just want to have a clean slate.

A:Deleting data in an Autonumber keyed table

Delete all the records and then perform a Compact & Repair.
Relevancy 52.46%

Hi there I was pointed in the direction of this site in the hope that I may get some help with a small problem. I have put together a fairly basic website that records scores for an online gaming event that I am hosting. The points given out go towards 2 sets of scores (an individual score and a team score) all of this is working perfectly. However what I could now do with doing is converting these 2 sets of scores into 2 league tables enabling us to update the website easily.

I am told the thing I need is something called a pivot table, but I have no idea how to set one up. Is there anyone who can help out here please.


Relevancy 52.46%

Hi everyone,

I have a macro that retrieves some data to excel and populates an excel table (listobject) using vba. The table is large (8000+ rows and 6 columns). There are another table that does some calculations based on the first table. Although the calculations is set to manual, when I send data to the table , I have an impression that the second table is recalculated too, because it takes a lot to add the data (but if I remove the second table it takes just a few seconds compared to 1,5 minutes).
The same is with the table resize.

Can somebody to advice on how to set excel so it calculates everything at the end of the code and not when the data is added to the table. Currently this settings are in place:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Using Excel 2010 and 2007.

Relevancy 52.46%


I have created new book and i copied the source data and pivot table from the file, but all my pivots are referring my old data source but i want to change into current active sheet source data. I have used the below codes but i dont know how i can use the entire sheet range;

Sub RefreshPivotTables()
Dim ws As Worksheet
Dim PT As PivotTable
Dim SourceName As String
x = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
SourceName = Worksheets("Overview_Data").Columns("a1:BN" & x)

For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = SourceName
Next PT
Next ws

End Sub

i am getting error code 1004 application defined or object defined error.

Please help me on this.
Relevancy 52.46%

I'm looking to return the source range (will be in another worksheet within workbook) for a pivot table in visual basic. Preferably as string.

I have 1 worksheet with 4 pivots. Each tables data originates on a separate worksheet within the same workbook as the pivots. I know how to do this ad-hoc...set the range equal to whatever sheet based on the name of the table itself, but I would like to be able to apply it to a number of different workbooks.

Any ideas?

Relevancy 52.46%

I ADO Connection Solved: a Need table to make Data using String have an Access database that is currently connected Solved: Need to make a table using ADO Connection String Data to our company s main database through an ODBC connection In a week or so we ll be moving from using the ODBC to ADO I Solved: Need to make a table using ADO Connection String Data have figured out how to connect the two using the following ADO connection string Private Sub Form Open Cancel As Integer Dim conn As ADODB Connection Set conn New ADODB Connection conn ConnectionString quot Provider sqloledb Data Source SERVERNAME Initial Catalog NAME User Id ll Password LLLLLL trusted connection yes quot conn ConnectionTimeout Dim myCommand As ADODB Command Set myCommand New ADODB Command myCommand CommandText quot select from tblName quot Dim rs As ADODB Recordset Where I get stuck is how to connect this to tables and queries in my database Or better yet how to get the above connection string to build a table that I can use using VBA What VBA or SQL would I use to link the tables that I previously used ODBC to connect to my Access database I ve tried using the below but it hasn t worked to far Dim fieldCount As Integer fieldCount rs Fields Count ListView Cols fieldCount ListView AllowUserResizing flexResizeColumns ListView Rows For i To fieldCount - ListView TextMatrix i rs Fields i Name Next rs MoveFirst Count Do While Not rs EOF ListView TextMatrix Count Count For i To fieldCount - ListView TextMatrix Count i rs Fields i Next Count Count rs MoveNext Loop rs Close conn Close I know that the connection works b c I can do a debug Solved: Need to make a table using ADO Connection String Data print and it prints all of the information in the table into the MS Visual Basic quot Immediate Window quot I just can t seem to get the data into a manageable form I can use So any help is greatly appreciated Look forward to your response Best Zhouma nbsp

Relevancy 52.46%

I am having a problem Changing Data in a Acess Table column I have a access table called quot ITEMS quot with one field called quot IDNo quot and one field called quot ItemNo quot I have records IDNO field is thru and in the ItemNo field with the following same data string I want to in Solved: Changing Acess column Table Data a loop thru these records changing the to for Solved: Changing Data in a Acess Table column example to using an Input Box varible Attached is the small procedure of what I have Problem is it s not changing the string to string but it s looping thru all records Any help appreciated boaterjohn Private Sub Command Click Dim intCount As Integer Dim strNewItemNo As String DoCmd OpenTable quot tItems quot DoCmd GoToControl quot ItemNo quot strNewItemNo InputBox quot ENTER Solved: Changing Data in a Acess Table column THE NEW ITEM NUMBER quot quot NEW ItemNO IS quot MsgBox quot NEW ItemNO IS quot amp strNewItemNo input the no of records for intCount using input box intCount DoCmd GoToRecord acFirst itemno strNewItemNo DoCmd RefreshRecord Do While intCount gt DoCmd GoToRecord acNext itemno strNewItemNo DoCmd RefreshRecord intCount intCount - Loop End Sub nbsp

Relevancy 52.46%

i want to create table in xamp server in data base application but it is difficult for me so please help me>!
Relevancy 52.46%

I've made a silly - rebuild can data? file - with How a I not drive build table, a on mistake during a restore process and am hoping someone can suggest a fix that doesn't involve me repeating the process A while ago my PC died with a nasty virus that wiped my file tables on all drives including my external HDD If I plug any of them into a windows machine it just hangs Luckily the OS on my BT Homehub allowed my to still access the data on the removable drive so I didn't do anything to fix it for a while When access to the removable drive started to get a bit sketchy I booted my laptop into a linux OS and started moving data from my How can I build - not rebuild - a file table, on a drive with data? old removable How can I build - not rebuild - a file table, on a drive with data? HDD and the other HDDs from the old PC onto a new larger removable HDD However As the new removable HDD was a factory clean internal HDD in a caddie it needed to be formatted first Like an idiot I formatted the new drive into FAT using the linux OS instead of windows Data can be accessed easily by linux but when I connect the new drive to windows It's recognised and assigned a drive letter according to 'safely remove hardware' but not recognised by 'My Computer' or disk manager My computer doesn't show the drive but has my others the disk manager mmc wont 'load' at all How can I build - not rebuild - a file table, on a drive with data? Is there a way of building the FAT table without formatting the new HDD in windows and spending another days repeating the data rescue process

A:How can I build - not rebuild - a file table, on a drive with data?

You have something else wrong if you can't load disk management.

The drive can be raw [unpartitioned/unformatted] and still recognized by windows disk management. Even a disk formatted under linux should be readable after a "import foreign disk" is done in disk management.

"It's recognised and assigned a drive letter according to 'safely remove hardware', but not recognised by 'My Computer' or disk manager."

Very strange. How are you determining its being assigned a drive letter if that letter doesn't show up in My Computer?

Instead of mmc have you tried just going via control panel/admin tools/computer management/disk management?
Relevancy 51.6%

I Drive USB Table Corrupted Partition Backup Data External on have a Western Digital MyBook ES GB external hard drive USB and eSATA interfaces for data backups formatted from a Windows XP PC that I now use with my laptop running Windows -bit Home Premium Recently my son connected it to his Apple PC and tried to create a partition to back up his files Apparently this corrupted the partition table I can no longer access any data on the hard drive The drive does not appear in Computer but does appear in Device Manager and Disk Management I believe the problem is that Corrupted Partition Table on External USB Data Backup Drive the partition table formatted for Windows was modified by an Apple computer so that Windows can no Corrupted Partition Table on External USB Data Backup Drive longer tell what file system is on the partitions My son is gone for a month and I no longer have access to his Apple computer so I can't plug the drive into an Apple computer to check if the drive gets detected I looked at the drive using Windows Disk Management Acronis Disk Director EASEUS Partition Recovery latest version Partition Wizard None of them could do anything except delete or format the partitions I saw no repair option or ability to mark the partitions as NTFS file system The partitions on my external drive are still there but no file system is shown for most of them Acronis Disk Director shows the following information Disk GPT VOLUME CAPACITY FREE SPACE TYPE FILE SYSTEM STATUS EFI MB MB Basic GPT FAT Healthy Local Volume GB GB Basic GPT Unsupported Healthy Unallocated MB Local Volume GB GB Basic GPT Unsupported Healthy Local Volume MB GB Basic GPT Unsupported Healthy I believe the partitions were originally created with the NTFS file system My guess is that the partition table needs to be corrected to indicate that the partitions are NTFS I'm not sure how to do this or how to rebuild the partition table Is there anyway to repair the partition table and possibly access the data Thanks

A:Corrupted Partition Table on External USB Data Backup Drive

A big chunk is unallocated. Was some of your data there? If so try Partition Wizard Partition Recovery Wizard - Video Help.

See if PW can also run Partition Wizard Check File System which can recover some partitions that are not readable.

If not some success has been reported repairing the partition table using Disk Genius trialware: