Clean Up A Linked Table

Apr 21, 2005

Hi all,
I have a linked table ( see attached image ). In it are several lines with the same invoice number. 1 line with this invoice number has some info I need to retrieve by serial number ( but this line doesn't contain the S/N). My question I guess is how can I retrieve a SerialNumber along with the 2 right most columns from the image?
IE: 0067320, A20900, 502060, 2120202019 23

I hope this makes sense. I was thinking of using a make table query to somehow clean this up, but not sure what to place in the query.
any other thoughts?
Thanks
Kevin

View Replies


ADVERTISEMENT

Combo Box Based On Linked Table Needs To Pull Info For Sub-Form From Non Linked Table

Aug 31, 2007

I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)

Here's some basic info

Tables

EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number

Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text

Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo

Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)

you can see the link table in the EstimatesandParts Table

Now I want to use that link to populate a subform in the F_Estimates form

Forms

SF_Customers - SubForm

(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)

FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode

SF_Parts - SubForm
Default View -Continuous Forms

(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)

Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;

(Pulls info from the table Parts for input into a list of parts to be used on that project)

PartName : Text Box
UnitPrice : Text Box

(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )

(have tried a couple things to complete this task)

Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)

(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)

(tried to make control source for the txtPartName to)

=Forms!Parts!Partname

(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)

F_Estimates - Form

Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription

(all basic Text Boxes)

Employee_ID
Customer_ID

(Combo Boxes Select Customer and Employee from list of present ones of each)

SF_Customers
SF_Parts

(Both SubForms on the main form)

Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...

Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason

Thanks in advance for ANY and ALL help that I get from here

View 10 Replies View Related

Clean The Records

Jun 29, 2006

here is the scenario:

i have a table that includes large number of records (10,000+) we call it raw data

the process is to "clean" the data by eliminating certain records: example eliminate records of Property Name that includes "restaurant" "golf" resort" etc....

The list of words that we want to eliminate (ie restaurant, golf, resort, etc...) is included in a separate table.

How can we produce a new table or query with cleaned records?:confused:
Thanks

View 2 Replies View Related

Clean A Folder Contains

Jun 17, 2005

I've built a Database which creates XML Files to import into another program. It just adds files to the program in a batch, so ever time I run the Export I want to Delete all the existing files for the holding folder. Have anyone done this before?

View 1 Replies View Related

How To Clean Out Multiple Tables With One Query?

Mar 2, 2007

Is their a way to edit the following query so that it will delete from more than one table?

DELETE FROM tblSample

Or do I need to write a seperate query for each table I want to delete?

View 4 Replies View Related

Clean-up Access 2003 Database: Prep For CSV/TXT Format?

Apr 18, 2007

Hi,This is my first post here... I hope I am not asking a really stupid question. :)Basically I need to cleanly export an Access 2003 database to a TAB-delimited file... The database in question has 1406 rows and several columns... some cells have nothing in them -- this is a "contacts" database.Long story short, I need to export-out as TAB-delimited (or comma)... I have done this, but when I view my results in Excel, it looks like some things are messed-up... I think there are some tabs hiding around the database that I am not aware of.Any tips on cleaning-up my database for export to CSV (tab/comma)?Also, I have one field that is a dropdown list... When I export it out as CSV/TXT I only get a number and not the actual value of the chosen drop-down... Is this normal? How do I get the actual value and not just the number?Basically, I would like to just normalize this database and get it into a perfect-looking CSV/TXT TAB/Comma delimited file. :)I would be willing to pay for this help... I do not have much cash, but I really want to make sure I get this done right the first time.Many TIA's!Cheers,M

View 3 Replies View Related

General :: Importing Objects From Secured To Clean Database

Jun 3, 2015

I have an access database that was set with security about 10 years ago.

I have now created another database in 2010 and I am importing all the objects to make a clean non secured database.

When I import tables it imports system table objects like MSysRelationships, but as it already exists it adds another MSysRelationships with a 1 on the end (MSysRelationships1).

Should I delete the original MSysRelationships and then rename the MSysRelationships1 as MSysRelationships.

There are numerous others like MSysAccessstorage, MSysACEs etc...

I am gathering that if I don't rename MSysRelationships1to MSysRelationships then the relationship data would be incorrect and the database wouldn't work correctly, but maybe I am wrong.

View 9 Replies View Related

Modules & VBA :: Attempting To Clean Up A Free Form Text Box

Jan 14, 2014

When we are managing incidents, users have decided on their own method of recording the incident... here are some examples

INC002546
INCIDENT002546
2546
INC# 2546
Inc# 2546
inc# 2546
Inc # 2546

The correct way is the following:
Inc# 2546
^ capital I, no space between "c" and "#" but a space AFTER the "#"

Then the 4 digit long number.I was thinking along the lines of trying to identify the number, removing everything else and then placing "Inc# " before it...
currently we allow notes, which should be made like such: Inc# 2546 - Notes.however, people have their own method for this too (however most of them are at least after then incident number so that makes things much easier)another issue is on the same database, some manual requests also appear which have different entry format: RQ# GK034LW2052 but that contains a mixture of letters and numbers... so when identifying the request number... as long as I make sure that it is a number and not a string of numbers and letters, I should be ok..

View 8 Replies View Related

Linked Table -- Database Name Linked To Table

Oct 4, 2005

Hi,

I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.

I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.

If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.

I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.

Can anyone point me in the right direction??

Thanks

View 4 Replies View Related

General :: Copy Data From Local Access Table To Linked SQL Server Table?

Jan 13, 2014

I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple

Code:

DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"

This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).

I would like to copy the data in a bulk operation, or operations that I can execute programmatically.

View 1 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

May 28, 2015

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

View 8 Replies View Related

Extracting Data From Linked Db2 Table Using Access Make Table Query

Aug 29, 2006

Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?

Thanks for your support
GinnyP

View 1 Replies View Related

General :: Possible To Have Data From Linked Table Automatically Update Into Existing Table?

Aug 17, 2012

I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.

There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.

Is it possible to have the data from the linked table automatically update into the existing table?

View 1 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Table With Country, ShipCountry, BillToCountry Linked To One Coutries Table

Nov 13, 2007

I would like suggestions on how to "properly" set up the tables and relationships required to do the following.
We have a customers table that can contain a Country, ShipCountry, and BillToCountry. All 3 countries could in theory be different. I want all 3 to store the CountryID from the Country lookup. I am trying top avoid a circular reference or any data contention issues. What is the proper method to do this?
Thanks very much in advance!

View 4 Replies View Related

Append Records From An Access Table To SQL Server Linked Table

Jul 21, 2006

I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.

The following error message occurs:

"ODBC -- insert on a linked table 'linked tblname' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)

I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.

What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.

I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)

View 1 Replies View Related

Forms :: Showing Data From Linked Table And Local Table

Nov 21, 2013

I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.

Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".

View 2 Replies View Related

Reports :: Select Most Recent Record From A Table That Is Linked To Another Table

Jul 13, 2014

I have a basic database design, well I think so anyway. It only has two tables:

1. tblClientInfo
2. tblNotes

Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.

I then have two forms:

1. Claims Loss Form
2. tblNotes_DatasheetSub1

So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).

Example of a print out of what it is doing:

John Doe - 123 Easy St - Notes 1 (Most Recent Note)
John Doe - 123 Easy St - Notes 2 (Previous Note)
John Doe - 123 Easy St - Notes 3 (and so on...)

It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.

How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?

I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.

I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.

The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.

View 2 Replies View Related

General :: How To Cleanly Convert A Linked Table To Local Table

Apr 1, 2015

I have a database that is designed with a front and back end, with the FE linked to the BE. Some potential customers want to play around with the database, and the easiest way for me to get them access to it would be for me to combine the FE and BE and just give them a copy of that. how to link a table, and I know how to import a table. What I'm unsure of is how to cleanly convert a linked table to a local table.

View 2 Replies View Related

Access Error 3027 When Using VBA To Append Table To A Linked Table?

Apr 7, 2014

I have two tables, submit and imgdest. Submit is edited by front-end users to load pictures for back-end users who then delete the images when they're done with them. Submit is edited by a form, in the form I've placed a button (Command37) that has code:

Code:
Private Sub Command37_Click()
Call InsertData
MsgBox "Completed", "0", "Completed Backup"
Exit Sub
End Sub
Private Sub InsertData()

[code]....

This code was working for a short while, now anytime it's run I get error 3027 - Cannot update. Database or object is read-only. However, I can open the linked table and manually change information in it with no problems.

View 14 Replies View Related

Local Table And ODBC Linked Table Don't Have Same Filter Options

Dec 31, 2014

In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.

My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.

View 3 Replies View Related

Tables :: Data Entered In Table Linked With Other Table

Nov 11, 2013

I have two table

1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID

i want to entered data in Saving with each and every EMPID with Master

View 1 Replies View Related

Forms :: Update Table From Text Box Already Linked To Another Table

Dec 13, 2013

I have a form that contains the following: Combobox, (Lists BadgeNum from tblPersonnel)

2 Textboxes (LastName, FirstName) populated via code from the combobox using info from the same tblPersonnel.
2 labels (one containing Date, another containing Time)
ToggleOnButton (Valueof 1)
ToggleOffButton (Value of 0)
Savebutton

This form is basically used as a cheap police timeclock. All Im trying to do is when a user chooses their name from the combobox, clicks ON or OFF and then save, is just write the much of the same info to a table. Specifically, BadgeNum, DateIn, TimeIn, DateOut, TimeOut.

View 4 Replies View Related

Copy Linked Table Into A Local Table

Jul 30, 2006

Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.

The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).

View 2 Replies View Related

General :: Linked Table Manager Looking For Old Table Name

Jun 20, 2014

I have a database that is used in our office. It is split with the backend stored on a network share.

I need to make some updates, and to do maintenance I usually make a copy of the frontend/backend to my desktop, and use the linked table manager to switch to using the local copy of the backend so I don't mess up the main data. All good so far.

however, I recently added a new table, and originally called it "overRides", I then decided that "adjustments" was a more suitable name and changed it.

Now whenever I try to switch from the main backend to my local copy I get a message when relinking "adjustments" that Access cannot find the object "overRides" and to make sure it exists and the name is spelled correctly...

I have been just deleting the linked table and reimporting "adjustments" but this issue keeps coming back and it's driving me nuts!

How can I make Access forget that this table used to be called "overRides"?

View 6 Replies View Related

Linked Table Look Up ??? HELP.

Jan 29, 2005

OK, so now in my Dbase Ive got the main data, and another table with 2 fields in,

in the second table its a part number and a weight,
on the main data I need the user to select the Part number with a combobox that the weight is filled in the maindata automatically, I dont understand Relational Dbases, yet so if anyone knows how I can do this, please explain in little words, lol


I did manage to do this earlier in the dbase with box sizes and weights but as there are only 4 size of box I coded it into he VB, but with the caps there could be up to 100 different caps.

Anyone HELP PLZ.

View 4 Replies View Related







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