Tables :: Changing Multiple Records Using MS Access?

Aug 28, 2013

I am connecting to a table in our accounting software using MS Access ODBC Connection. I would like to change the value in a table for every record in the table 7,000+. In the latest version of the software a new field was added to the Vendor Table that by default is unchecked. I would like to have this field checked for each vendor. Is there a way that I can change all the records at once instead of going row by row to make the changes?

View Replies


ADVERTISEMENT

Forms :: Changing Multiple Records At Once

May 13, 2014

i have a splitview form that has a sub form which is a continuous form. On the continuous form there is a start and end date for each record. i would like to be able to change all the start dates on the continuous form by selecting a date once and hitting update or something like that.

so what i have going on is i have designers building scaffolds and adding articles to the scaffold (thus the continuous list) then a few months down the road the site planner will schedule the scaffold to be built and torn down. this will be done on this split form noted above. once that is done i need to have a date and an action applied to each article in the scaffold because i need to track usage of all the material per peice on a job site.

View 4 Replies View Related

Changing Structure Of Multiple Tables

Oct 1, 2006

I want to add 3 new fields to the structure of around 80 tables (all with identical structures). Is there a way to automate this process?

Paul.

View 8 Replies View Related

Changing Information (Same Info) In Multiple Tables

Apr 7, 2015

I have now been asked to create 'something' (form, query, whatever) that will:

Allow the engineers to change the date in multiple tables for parts; and,Change (in)activity status information in those tables; and,Delete the same obsolete part(s) from two tables and add that information (one entry) into another table.

View 1 Replies View Related

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

View 4 Replies View Related

Changing Case Of Data Already In Access Tables

Dec 8, 2006

My database is now live within our office and is working fine.

The databases which I have imported the data from, where done by my dad, who being old fashioned, used CAPS for everything he typed. So for quite alot of the data, its all caps, and it would be great, but not essential if we could revert the text to normal sentences.

Is there any trick or code that I could run that would go through certain tables and reformat the text in to a better format??

View 6 Replies View Related

Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?

Nov 21, 2012

I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).

I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.

View 3 Replies View Related

Combining Multiple Tables With Multiple Records

Jul 16, 2007

Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -

I have units A, B, C
Parking units p1,p2,p3,p4,p5,p6
Storage units s1,s2,s3.

Unit A uses parking units p1, p2, p5 and storage unit s2.

Unit B uses parking unit p3 and storage unit s1.

Unit C uses parking unit p4, p6 and storage unit s3.

How do I write a query/report that would show:

Unit Parking Storage
Unit A p1, p2, p5 s2
Unit B p3 s1
Unit C p4,p6 s3

Thanks,
Chester Campbell
ccampbell@jfreed.com

View 3 Replies View Related

Import Excel Data From Multiple Sheets Into Multiple Tables In Access

Aug 25, 2012

I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.

For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...

I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"

Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.

Any other ways to import the data?

View 1 Replies View Related

New Records On Multiple Tables With One Form

Jun 15, 2005

Hey Guys,

I have 2 tables (tblSET, tblPROJECT) that have a relationship between them in the form of strPROJID (Project ID)

I need to make a form that has all empty fields in it, and when filled in distributes the entered data to the corresponding field in the corresponding table. I have tried using the design wizard and just putting the fields from all tables onto one form, but the "new record" button is greyed out when I do that. I figure this will include coding of some sort but I am far from a coder.

Thanks ahead of time,

JVermast

View 1 Replies View Related

Tables :: Adding One Value To Multiple Records

Jul 31, 2013

I am new to Access here, and looking to start up a database that will contain data for thousands of footballers.

I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).

How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?

View 3 Replies View Related

Listing All Records For Same Field In Multiple Tables

Oct 27, 2006

Hi,

I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?

Thanks,

Richard

View 6 Replies View Related

Adding Records To Multiple Tables Using One Form

Sep 20, 2004

OK I'm totally lost again.

What I want to do is;

Use one form to add records into several tables.

I have seperated my data into seperate tables and set up one to many relationships.

tblincident
--------------
Key#
Report#
Time
Date
Location

tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.

tblItem
----------------
Key#
Make
Model
Serial#
etc.

What i want to do is use one form to enter all this data into the different tables.

There can only be one record in tblIncident that can match multiple records in the other tables.

i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.

I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.

So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.

I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?

Any help is appreciated.

View 1 Replies View Related

General :: Add And Update Multiple Records To Different Tables

Jan 12, 2013

I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.

When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.

If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.

Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.

For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.

Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.

By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.

View 3 Replies View Related

Combo Box To Find Records From Multiple Tables

Jul 30, 2013

I have a main form that has fields from different tables. This is a research study, so each form enters data into different tables. Well, each record is a person's data. Instead of scrolling through each record, I need an option on the main form that allows me to search for a specific person and have their data populate into the form.So far I am able to make a combo box that pulls up the record from one table. Well, HOW do i do it from ALL the tables!?? Do I have to make a query?

View 3 Replies View Related

Tables :: Combine Multiple Access Tables Into One

Mar 7, 2013

I have multiple tables that are created from some software that puts them each into the access database. each table is one year of data. the software cannot put all years into one table because of memory. I want to combine these tables into one. In addition, I need to do some manipulatins: the dates come over as MM-DD-YYYY strings and I need to convert them to dates MM/DD/YYYY, and also I need to parse a string to extract the General Ledger account number. I know how to use this easily from within Excel VBA using dateserial, and Instr etc., but now sure how/if I put some code into access and have it called to do this for me automatically. Each night the most recent year is automatically updated in access, so the combination file needs to be refreshed whenever the most recent year file changes.

I currently doing all of this in excel: first initialize the NewCombinedTable, then one by one dumping each year file into an excel worksheet, doing the work needed, then appending it to the NewCombinedTable. However, I think it would work much quicker if I was able to trigger the Combination to happen in Access along with the data manipulations.

I also need to know whether DAO or ADO is best for me to use with 2010...

View 1 Replies View Related

Reports :: Joined Tables - Returning Multiple Records

Apr 10, 2014

I a report based on query based on joined tables. Im using FK and PK accordingly in the tables and have a junction table.

I've attached a sample (removed all unique identifiers for privacy in case you're wondering).

Table structure as follows:

Employees tbl - Junction (License Link tbl) License No tbl, License Class tbl, Endorsements tbl

So the query runs on all of these tables.

When I build a report on the query, Simon shows up Six times. I suspect because he has six endorsements.

How do I produce a report on this table/query structure so that I get:

Simon.
Class, 1,2,3,4,5
Endorsement F,R,T,W,DG

View 1 Replies View Related

Tables :: Make Table Returns Multiple Records

May 11, 2013

I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.

View 1 Replies View Related

Adding Records Into Multiple Tables At A Single Moment

Dec 2, 2011

Supposing you have 4 Tables

1.)User
2.)Admin
3.)Teacher
4.)Pupil

Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.

Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.

Question is: Is that possible? If yes, then how??

View 5 Replies View Related

Write A Query That Will Return Records From Multiple Tables

Dec 19, 2012

I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.

The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.

View 14 Replies View Related

Reports :: Counting Test Records From Multiple Tables In One Report?

Apr 22, 2014

I have been trying to create a report to count equipment tested between two dates:

I am using Access 2000 (old I know) and I have 21 different tables with the fields laid out the same. For the moment, I will list five of the tables: Servers, Laptops, Printers, Workstations, and Monitors. The criteria I draw from each table are the fields Model#, Part#, Serial#, Test Date, Retest Date, and Technician.

I can create a report from a query (say laptops). The criteria I is BETWEEN[Enter Start Date]AND[Enter End Date] under the TEST DATE field. Works great! In my report I use =Count(*)&" "&"Unit(s) tested" & "Between "&[Enter Start Date] & " and" & [Enter End Date].

That works great too.

I am trying to create ONE REPORT using ONE Date range and display how many units were tested:

Example:

Units Tested between March 1, 2014 and March 31, 2014

Servers 9
Laptops 23
Workstations 15
Printers 18
Monitors 31

If I can get these five tables, hopefully I can add additional tables to the same report as I need them. I am not savy with VB code, but will give it a go with some direction as to where to put it and how to add to it.

View 5 Replies View Related

Tables :: Copy And Paste Text Field To Multiple Records

Aug 10, 2015

I have been unable to find a simple way to copy a text entry from a Table Field and paste it to multiple records in Datasheet view.The Toolbar 'Paste' procedure copies to the selected record only and I am unaware of how to select multiple records, as per a spreadsheet procedure.

View 2 Replies View Related

Reports :: Multiple Tables - Pull Information From Records Based On Certain Key Fields

Nov 5, 2014

I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.

Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.

I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.

So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".

So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...

Got relationships between:
"contact name" fields in both "table2" and "table3"
"project number" fields in both "table1" and table2"

In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?

Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????

View 6 Replies View Related

Tables :: Unable To Create A Database By Compiling Records From Multiple Excel Sheets

May 22, 2013

I am trying to create a database by compiling records from multiple excel sheets. however, since most of the clients still uses the excel sheets to enter records, I am thinking if it's possible to create a linked table in access from these excel sheets that are hosted on an online server.

also is it possible to merge multiple linked tables together? if they have the same field range, so they will append when the linked tables gets updated.

View 3 Replies View Related

How To Create Multiple Records In Access

Jan 5, 2012

I am relatively new to Access and have a simple database which I use to generate Publication numbers for our new publications. I would like to have a text box and button on a form that will generate duplicate records of the current record the same number of times as specified in the text box.

E.g. If I need 6 copies of a record I would like to enter 6 in a text box, click a command button that will generate 6 duplicates of that record.

The code in order to do this 'on click'.

Table = Publications
Form = Publicationsfrm
Text Box = Lines
Command Button = Submit
Primary Key = PublicationNo

I have tried the following code but it is not working.....

Dim i as Long, myRS as Recordset
Set myRS = CurrentDB.OpenRecordset("Publications")
For i = 1 to Me![Lines]
With myRS
.AddNew
![PublicationNo] = Me![PublicationNo]
.Update
End With
Next

Alternatively if I have a duplicate record button that uses a macro is there a way to run that macro the specified number of times in the 'Lines' text box.

View 14 Replies View Related

Problem In Vba Code To Access The Records From Access Tables

Jul 23, 2007

Iam using the following code for inserting the values from access form in to the access database table ITEMS, but iam getting compile errors, may i know where iam going wrong in the code.

Also help me in updating, deleting and viewing of records from database table ITEMS to the form when i run the program.

Following is my code :


Private Sub ADD_Click()

On Error GoTo Err_ADD_Click

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection

'Create an instance of the ADO Recordset class, and
'set its properties

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM ITEMS"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
End With

'Set the form's Recordset property to the ADO recordset



DoCmd.RunSQL "INSERT INTO ITEMS(ITEM_CODE, ITEM_NAME, ITEM_CATEGORY) VALUES('" & ITEM_CODE.Text & "','" & ITEM_NAME.Text & "','" & CATEGORY.Text & "')"



Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing

Exit_ADD_Click:
Exit Sub

Err_ADD_Click:
MsgBox Err.Description
Resume Exit_ADD_Click

End Sub



compile ERROR as follows:

you can't reference a property or method for a contro unless the control has the focus.

View 13 Replies View Related







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