Query From Linked Data

Jan 19, 2007

I am trying to generate a query from a series of linked tables in a "linear" arrangement. My central table is an employee table. I then have two branches for phone data and office data, each in a many to many relationship with the employee table, so I have a linking table between.

Phone<-->Employee_Phone<-->Employee<-->Employee_Office<-->Office

I am trying to generate a directory that lists for each employee their phone number and their office number. Whenever I try to generate my queries however, I get each phone record for each office record. If I have 4 phones and 2 offices for an employee, I then get 8 rows in the query. Is my table setup just awful (my attempt at normalizing my data as many offices and phones are shared) or is there a clever way to generate a query I haven't yet discovered. Any help would be great!!! (lest I start )

View Replies


ADVERTISEMENT

Linked DB Query Which Allows Editing Of Data Through A Form

Dec 20, 2005

Hello,

I am a relative newby to Access, Can someone please offer a solution to my problem.

I have a DB which lists current order references, I also have a linked spreadsheet from a customer which references their Ref number to our S/order no & Item no.
I have set up the relationships for the common data between both tables.

if I create a Query from either table individually the data can be edited but when a query is created from both sources the data cannot be edited.
this also happens if the spreadsheet is imported to its own DB.

I would really like to create a form which displays both sets of data for editing, marking progress etc.

Can data from linked tables be edited in a form ?

Regards

Andy

View 6 Replies View Related

Queries :: Concatenate Field Data Into One Cell In Query According To Linked Table ID?

Mar 8, 2014

i need to Concatenate a fields data into one cell in a query according to linked table ID....

View 3 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

Linked Data Vs Importing

Apr 25, 2006

hey all,

here is a general question:

i am creating a database that relies on data in 3 different excel spreadsheets being updated by 3 different users. that is, each person is responsible for updating the Dates for their spreadsheet.

my database needs to pull these dates to extract the most current info every time i run my query.

my question is, is linking to these excel spreadsheet a valid method in completing this task. other that that, i guess i would need to import the data form excel every time each one of the 3 users updates thier stuff.....which sounds horrible.

any thought/suggestions would be great.
T

View 1 Replies View Related

Not Allowing LINKED Data To Be Updated

Jul 27, 2005

I have a very simple database used by 3 users at a time. It is split into BE and FE. One user accesses the DB with queries only so I have created a DB with the master file linked - and literaly nothing else, She will then create and save all of her queries here. But - I want to keep her from accidently updating the file from here. I can't seem to find a simple solution to simply not allowing her to update. Any ideas? Thanks - Dika

View 3 Replies View Related

Enter New Records By Specifying Linked Data?

Aug 10, 2005

I've posted in general because I really don't know what approach is best for this requirement. I can't even come up with a meaningful, yet succinct title.

Here's the problem. I have tables:

tblApplication - defines an application (name)
tblServer - defines a server
tblInstance - defines a partition on a server (defined in above table).
tblApplicationInstance - defines a specific instance of an application on an instance on a server (i.e. ties the above three tables together)

The tblApplicationInstance table has an autogenerated ID field as primary key, and foreign keys to tblApplication and tblInstance (and thus through this to tblServer).

All well and good. Now the next table

tblApplicationUse - defines that a specific project (a foreign key to another table but I don't think it is an important factor here) is using a specific application instance over a date range.

The question. How to allow entry of new tblApplicationUse records without having to find and enter the ID from tblApplicationInstance. Rather, I want the user to specify the Application, Server and Instance, but be limited to only those that are defined.

I tried a simple query, thinking it may allow me entry, but not so. I've been building some simple forms for query parameter prompting lately but am fairly inexperienced with these. The crudest form of prompting I can think of is to simply apply a drop-down to the ID field (in tblApplicationUse) and use a multi-column format here. But it ends up very wide and is less than ideal.

Can anybody give me ideas on the 'proper' way to do this?

View 1 Replies View Related

Linked Table Data - Does It Travel?

Nov 29, 2005

In a linked table Access solution where the tables reside on the network, and each user has a front-end on their desktop, does all the data travel over the network to the clients desktop to do the query, or since the backend is Access and it can obviously understand the query, does the backend do the query and just return the resultset?

Thanks

View 2 Replies View Related

Importing Data Into Linked Tables?

Aug 11, 2005

I'm trying to perform a massive data dump. I have about 1,000 records that need to be added into my database. This is coming off an Excel spreadsheet.

I don't know if this is possible:
I have a table, tblTorders, which is linked to the table, tblItems.
When I open the tblTorders, I can click on the plus sign to expand and see the related data in tblItems.

How can I import data to populate the fields in both of these tables? I can't use the import function because you can only select to import it into one table.

Do I do it through a query.... I'm not sure where to go on this one.

Thanks so much

View 4 Replies View Related

Script For Getting Linked Data Into Access

May 23, 2006

I have been exporting data to Excel from a Brio/Hyperion queryand then importing that data into an Access table. The problem is that this has to be done daily. I need a link to "live" data in our datawarehouse, but cannot use Access to link to it. Can someone give me an example of how to write a script in a text file that would be used as a go-between for Access and the data warehouse and give that link?....Excuse me, but an earlier post of mine was very similiar to this - just that I didn't pose the question properly. Thanks

View 1 Replies View Related

Can't Delete Data In Linked Table

May 1, 2015

My goal is to get a huge amount of data paired down so that I can work with it in Excel.

I started with a 14 million record text file provided by a government agency. I used a text editor to break that into 7 2-million record files to fit into Access.

I figure I can filter each of those files down to 500,000 or so records, so that I can manipulate them in Excel. The problem is, when I open a text file in Access, Access insists on linking it, and then I can't delete data from a linked file.

View 3 Replies View Related

Missing Data From Linked Table

Jun 27, 2012

I have all my data sitting on a server in SQL. Within Access, I have linked tables pointing at SQL tables. I display the data on an Access form. Somewhere in their it is dropped a few records. This system is used for in and out processing and occasionally misses a few individuals. Could this have anything to do with the method in opening the table, ie dbopenTable vs dbOpenSnapshot.

View 1 Replies View Related

General :: Access Data Export Into Excel As Data Linked To Excel

Oct 21, 2012

how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;

DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"

Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.

View 5 Replies View Related

Updating Fields And Data On A Linked Table

Nov 14, 2007

Hi everyone,

I was wondering if this is possible to do:

I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?

If anyone can help, that would be greatly appreciated. Thank you in advance.

Bear

View 1 Replies View Related

Editing Data In A Linked Excel Sheet

Mar 28, 2008

Hi all. I have an excel spreadsheet that pulls company information off of our internal system that is linked to an access database table (let's call it "General"). The General information is current and is updated within our internal system (but through the Access side will not need to be updated), however, I have a separate table (let's call it "Detail") that has information (employment, revenue numbers) that I want users to input through access. The two tables are joined through a relationship, however, b/c General is a linked table I am a) unable to assign a primary key and b) unable to edit the Detail information in a form view. I know this is related to Microsoft's limitation on excel linked table, but I was wondering if there is a way around it besides copying the General data into an access table. Would be grateful for any help!

View 2 Replies View Related

Converting Data Types With Linked Tables

Aug 3, 2007

Hi,

I have 2 linked tables, I would like to join these tables in access, however the first table's field is a memo and the second table is a number. I have tried to cint() the field in a query but I get an error message "Explicit conversion from datatype text to smallint is not allowed"

Any ideas how to get around this problem?

Thanks

Michael

View 4 Replies View Related

Copying Data From A Linked Subform To A New Table

Dec 3, 2005

I have linked a SQL table to my ms access database. In order to view the SQL table as read-only and still be able to update the access table I have setup a form with a subform.
I know it would be a dublication of data but is there a way thru code of copying the data on the subform to my access table. I was hoping I could put a button on the form that would copy this data. My idea is after the copy is done all the information I need will be in my access table. I'm new to this linking idea and am worried I will only have half the information I need if the link breaks etc.
Thanks

View 1 Replies View Related

Multiple Forms Data Linked To 1 Record:

Feb 6, 2006

What I have is a form with a combo box that lets a user select the Letter Type. For 1 letter in particular I want it to open up a subform(s) that holds "options" that users can click on. My question is how would I get these options / multiple options to link to 1 record that correlates with the Letter Type?

View 2 Replies View Related

Forms :: Get Data From A Linked Excel Sheet?

Mar 6, 2013

I have a excel table (lets say the format is as follows)

col1 col2
dataset1 value1
dataset2 value2

(where col1 and col2 are the column names and dataset x and value x are the respective data values of each column) I have a access form and it has a combo box and a text box From Combo box I need to retrieve data available in col1 o excel once a particular dataset in col1 has been selected by Combo box the respective value in col2 should be displayed in text box in access form.

I was trying to create a linked table in excel to access ( I don't want to export data from excell to access since my excell sheet is getting changing time to time. so i don't need to change the access table every time)

for combo box a simple query like below works to select the col1 values from sheet 1

select col1 from Sheet1 (where Sheet1 is the linked table name in access)

I wrote a code similar to below using DLookup to get respective values from col2 however it gives a error '#Name?

=DLookup(col2,Sheet1,col1=[Forms]![FormName]![ComboBox Name])

(here sheet 1 is the linked table name in access, I didnt give the absolute path of the excell file)

this returns the above #Name?

View 1 Replies View Related

Modules & VBA :: Get Data From A Linked Excel Form?

Mar 26, 2014

I have a file "MatchingData" thats linked to another file "Clist" . What I'm looking for to open "MatchingData" and update from "Clist" on opening. I don't know if I can do this without opening "CList". I have a feeling that I may have to open the "CList" (Hidden), update,then close "CList".

View 2 Replies View Related

Tables :: Appending Data To A Linked Table?

Feb 6, 2014

I have a table in MS Access 2010 that is a link to an external data file in .csv format. I assumed that it was not possible to append data to a linked table until recently. I appended 3 records to the linked table and discovered that the 3 records were appended to the table and the external data file it was linked to.

What am I missing ?

View 8 Replies View Related

Preview Data In Label Linked To Table

Apr 9, 2014

I have a table that have about 5000 records. Also I have a report that include a label which is linked to this table.

The problem i have is that when i have heavy data in the table like 5000 record and above, the label is not taking data from first to last record from the table, it is taking from record 1200 reaching record 5000 then it takes record 1 to 1199.

Therefore when label is previewed, we are viewing data on label from 1200 to 5000 then from 1 to 11900 and not from 1 to 5000

On the other hand, when i don't have heavy data in the table, everything is working good and the label is getting its data from first to last record successfully.

View 1 Replies View Related

ID Fields: Data Type Differs In Linked Tables

Apr 6, 2006

I have a database consisting of three linked tables, with ID as unique identifier (primary key).

I would have expected the data type for my unique identifier, especially in the main table, to be Autonumber. However, I discovered with some surprise that the primary key (ID) in

Table 1 (Main table) is data type: number, field size: double

whereas in table 2 and table 3 the ID is data type: autonumber (field size: long integer)

It must have happend "SOMEHOW" when I divided the original single table.

Is this as it should be? Or could it lead to trouble later on?

If not as should be, is there anything I can do right now to rectify the situation with risking upsetting the relationships and all?

What precautions should I take, apart from taking a backup?

Thanks for your help.

Adrian

View 2 Replies View Related

Auto Fill With Data From ODBC Linked Table

Jun 8, 2007

Hi All,

Although familiar with CR, SQL and other such things. I have never had cause to use Access all that much.... Until Now!

My problem is easiest explained by my requirements....

I have a db table called Source. The data for this Table is populated by a form called Technical. Technical containes a series of fields (SO No, SO Line No, WO No etc etc). Some of these fields are lookup fields linked directly to the SO_Header Table in our main ERP software via and ODBC connection. The result is the SO No field is populated with a list of Sales Order No's as they exist within the ERP software. Within the Technical Form I have also the Customer Name.

This is where the problems begin. Within the ERP software we have two tables which I need to use: SO_Header and Customer. The SO_Header contains all the Sales Order info and the Customer table contains all the customer info. What I need to do is populate the customer name field in by db with the relevant customer based on the SO No chossen. However the Customer Name does not exist in the SO_Header table. Instead both the SO_Header table and the Customer tables contain a field called Cust_Code.

Now if I wanted to link these in Crystal Reports I would link from the SO_Header table to the Customer table using the Cust_code field. This would give me the corresponding Customer name based on the Sales Order No.

How can I get the correct Customer Name to appear automatically in my Access form based on a SO No chossen by the user?

I would realy appreciate any help on this! If you need me to clarify anything please ask.

Thanks in advance!

RobV

View 1 Replies View Related

How To Detect When Data In A Linked Table Is Idepenently Updated

Mar 17, 2008

I have developed an application using MS Access and MySql tables.
The Access forms maintain/display orders that are stored in the MySQL linked tables. The MySql tables can also be updated independently by another process. The problem is that the Access forms do not reflect the backend changes when they are made by the other process.

For the moment I have implemented an on Timer process that causes a requerys for the forms every 5 seconds but this is not very elegant and causes the form screens to flicker.

Is there a better way I can automatically make the front end forms reflect changes to the back-end database when it is updated indepenently ?

View 4 Replies View Related

Tables :: Linked Table - Adding Entries To Each Row Of Data

Jul 2, 2014

I have a table that is linked to a survey and pulls data from the limesurvey backend.

The user would like to add a local field, so they can add entries to each row of data.

Is that possible ?

View 1 Replies View Related







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