relationships between tables

I have about 5 tables that I cannot enforce referential integrity
on... 3 of them have a common field, but the error messages I am
receiving are way above my head technically.  Any suggestions?  Or
even documentation on relationships (from the very beginning) would
help a lot!

Thanks
Mindy

0
melinda
7/11/2007 6:40:13 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
795 Views

Similar Articles

[PageSpeed] 37

First suggestion is to post the error messages you are getting.

It could be that you can't enforce referential integrity for a variety of 
reasons. To name two off the top.
-- No unique index on field in the table on the one side
-- Fields in many side already have values and there are no related values 
in the one side table

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<melinda.pluma@agedwards.com> wrote in message 
news:1184179213.045939.113610@k79g2000hse.googlegroups.com...
>I have about 5 tables that I cannot enforce referential integrity
> on... 3 of them have a common field, but the error messages I am
> receiving are way above my head technically.  Any suggestions?  Or
> even documentation on relationships (from the very beginning) would
> help a lot!
>
> Thanks
> Mindy
> 


0
John
7/11/2007 7:44:55 PM
On Jul 11, 2:44 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> First suggestion is to post the error messages you are getting.
>
> It could be that you can't enforce referential integrity for a variety of
> reasons. To name two off the top.
> -- No unique index on field in the table on the one side
> -- Fields in many side already have values and there are no related values
> in the one side table
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> <melinda.pl...@agedwards.com> wrote in message
>
> news:1184179213.045939.113610@k79g2000hse.googlegroups.com...
>
>
>
> >I have about 5 tables that I cannot enforce referential integrity
> > on... 3 of them have a common field, but the error messages I am
> > receiving are way above my head technically.  Any suggestions?  Or
> > even documentation on relationships (from the very beginning) would
> > help a lot!
>
> > Thanks
> > Mindy- Hide quoted text -
>
> - Show quoted text -

"The SQL statement could not be executed ebcause it contains ambiguous
outer joins.  To force one of the joins to be performed first, creat a
separate query that performs the first join and then include that
query in you SQL statement."

I have tried that... or what I understand to be these directions.  I
created a query, and tried to add it to this query, but I get the same
message... Also, this (the second one) query is not connected to
anything... I'm sure this effects it... but I really don't understand
relationships.


0
melinda
7/11/2007 8:04:15 PM
Ok, that message is telling you that you need to break the query into 
two (or more parts).

Drop one table from the query and see if the query runs.  IF it does 
Save the query as Part One.

Now Open a new query and add the table you dropped in the first query 
AND the query you just saved as sources.  Establish your join and run 
this query and see if it runs.

It helps if you post the SQL statement of the query that doesn't run. 
Sometimes we can figure out the problem from the query (Hint: View: SQL 
from the menu).



'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


melinda.pluma@agedwards.com wrote:
> On Jul 11, 2:44 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>> First suggestion is to post the error messages you are getting.
>>
>> It could be that you can't enforce referential integrity for a variety of
>> reasons. To name two off the top.
>> -- No unique index on field in the table on the one side
>> -- Fields in many side already have values and there are no related values
>> in the one side table
>>
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> .
>>
>> <melinda.pl...@agedwards.com> wrote in message
>>
>> news:1184179213.045939.113610@k79g2000hse.googlegroups.com...
>>
>>
>>
>>> I have about 5 tables that I cannot enforce referential integrity
>>> on... 3 of them have a common field, but the error messages I am
>>> receiving are way above my head technically.  Any suggestions?  Or
>>> even documentation on relationships (from the very beginning) would
>>> help a lot!
>>> Thanks
>>> Mindy- Hide quoted text -
>> - Show quoted text -
> 
> "The SQL statement could not be executed ebcause it contains ambiguous
> outer joins.  To force one of the joins to be performed first, creat a
> separate query that performs the first join and then include that
> query in you SQL statement."
> 
> I have tried that... or what I understand to be these directions.  I
> created a query, and tried to add it to this query, but I get the same
> message... Also, this (the second one) query is not connected to
> anything... I'm sure this effects it... but I really don't understand
> relationships.
> 
> 
0
John
7/11/2007 11:16:01 PM
<melinda.pluma@agedwards.com> wrote in message
news:1184179213.045939.113610@k79g2000hse.googlegroups.com...
> I have about 5 tables that I cannot enforce referential integrity
> on... 3 of them have a common field, but the error messages I am
> receiving are way above my head technically.  Any suggestions?  Or
> even documentation on relationships (from the very beginning) would
> help a lot!
>
> Thanks
> Mindy
>

Mindy,

Referential Integrity is a feature offered by relational databases.

In MS Access, referential integrity (RI) is enforced by a Relationship object.

When you--the database designer--establish a Relationship between two tables, it is
usually done so that you can be sure that values in a column in the first table are the
only values used in a column of the second table, and that values in the second column
cannot exist when there are no matching values in the first column.

From the first table, the most common column to use for a Relationship would be the
Primary Key.

From the second table, the most common column to use for a Relationship would be a column
that was used to store values from a Primary Key column in the first table.  This column
is called a Foreign Key (because it is really a key from another "foreign" table).

A Relationship object establishes a hidden index on the column in the second table, and
there must be a Primary Key or Unique Index on the column in the first table.

(This works on multiple columns as well.)


Why does anyone want to establish RI?


If you are a grocery retailer, at the simplest level you probably have an Items table and
a Prices table.

Items
  ItemID -- Primary Key
  ItemName

Prices
  PriceID -- Primary Key
  ItemID  -- Foreign Key
  Price

If you delete and item row from Items, and you have prices for those items in Prices, you
suddenly have a situation where no one can determine what any of those prices were
attached to (the item is gone).

Nobody wants that.

When a Relationship object exists between ItemID in Items and ItemID in Prices, the
database automatically checks to make sure this can't happen.  If you try to delete an
ItemID value from Items when there are any of those ItemID values left in Prices, the
query will fail.  If you try to insert a row into Prices with and ItemID value that
doesn't already exist in ItemID in Items, that query will fail.

This keeps your database neat and tidy.

You can use CASCASE DELETE and CASCADE UPDATE when setting up a Relationship.

CASCADE DELETE makes it so that if you delete a row in Items, and there are any prices in
Prices for it, the rows in Prices are automatically deleted.

CASCADE UPDATE makes it so that if you change an ItemID value in Items, any occurrences of
that value in Prices are also automatically updated.

There is no such thing as CASCADE INSERT.

----------------

You want to match every Primary Key's Foreign Key (Items.ItemID and Prices.ItemID type
combination) with a Relationship object throughout the database.

----------------

It is possible to establish RI constraints on columns other than Primary Key/Foreign Key
pairs, but it is fairly rare, except for recursive relationships, which are well beyond
the scope of our discussion.

----------------

Based on your further description in the other branch of this topic, I'm not sure that RI
has anything to do with your query's error.

IMO this: "The SQL statement could not be executed ebcause it contains ambiguous
outer joins.  To force one of the joins to be performed first, creat a
separate query that performs the first join and then include that
query in you SQL statement."

Usually means that there are column (or table) names in the query that cannot be resolved
(the database can't figure out what they belong to).

This is usually, but not always, the result of one or more missing table qualifiers (like
"Items.ItemID, Prices.ItemID" is fully qualified, but "ItemID, ItemID" isn't.).

More cannot be said (by me) without viewing your SQL.  (Open your query in Design View,
then View > SQL View, and then copy and paste post the code.)


Sincerely,

Chris O>


0
Chris2
7/12/2007 6:13:31 AM
Reply:

Similar Artilces:

Date formatting on a Pivot Chart's Data Table
I have a Pivot Chart that has a text field for series, a date field for category and a number field for data items. In the charting options, I've hidden the legend and shown the data table. When the data table appears in the chart, it shows the dates in the format: "M-d-yyyy hh:mm" I'd like it to show in the format: "MMM-yy" I can't find any options inside the Pivot Chart context menus or toolbar for applying date formatting on the Data Table. I've tried formatting cells in the Pivot Table and it applies them there, but still shows in the format listed ...

Pivot Table Data Transfer
Please help me? Thank you for reading. I have a working pivot table with over 50 entries. I need to take certain parts of the data to new worksheets so clients can not make changes. I need this marco to run each entry in the pivot table to produce the transfer data and then copy and paste the information into a new workbook for each entry in the pivot table with two forms(already created) with the data in it. Can someone please help me? Here is the code below: Sheets("Pivot").Select For Each itm In ActiveSheet.PivotTables("PivotTable3").PivotFields("Lessee"...

Contract Relationships
Hi, I am trying to create various many-to-many relationships to the Contract entity. One example is linking Contracts and Contacts via an intermediate custom entity called Contract Contact. A Contract can have many Contract Contacts and a Contact can have many Contract Contacts, so this should achieve the many-to-many relationship required. However, when I create my new Contract Contact entity and create a relationship to both Contract and Contact entities I encounter some problems: Firstly, I cannot create a new Contract Contact from a Contract, I don't get the new button, but I can fr...

Displaying items within pivot table fields
Hi Within a macro I've written I have the following syntax (which is not working); at the time that the macro reaches this line of code only the "ACCDNT" item in the "complaints_code" field within the "ComplaintsTable" pivot table is being displayed: With ActiveSheet.PivotTables("ComplaintsTable").PivotFields("complaint_code") .PivotItems("ACCDNT").Visible = False .PivotItems("ADVERSE").Visible = True End With Basically all I want to do is have the macro display the "ADVERSE" item instead of ...

appending data to ms access table using ms excel
Help, I am trying to update an Access table that has one field with a value that is coming from an Excel table. I looked through several cites are I have been unsuccessful. I believe that ADO would be needed to accomplish the task. If anyone has a short example of the code that would be needed in Excel to control access please let me know. Thanks in advance Here's a routine that I use to append daily stock price information to the MDB file where I keep historical quotes. I have a generic function that sets up the connection, rather than doing that in the sub itself. Prices() is...

Pivot Table #8
When I am tranfering data from Excel Spread sheet into the pivot table (several dates are involved) instead of it giving me the amounts in the rows it is giving me counts.Any suggestion? Is the option box not set correctly? ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** You can't change the default settings for the data fields. If a field contains blank cells, or cells with text, it will default to COUNT. Otherwise, it will SUM. Moe wrote: > When I am tranfering data from Excel ...

Data Source in Pivot Tables
I am working in Excel 2003. I have a bumch of expense catergories that I would like to put into a pivot table. I also have a month and year column. So I put my year in the column part of the Pivot Table Chart Wizard and my month is the row part, and then all my expenses in the data portion. This works to a point. As soon as I go to my Pivot Table and change my dad to only view 1 or 2 expenses I lose the rest of the data. I have to go back in and add the rest again. Why is it doing this?? Is there a better way?? What am I doing wrong? When you clear a checkbox in a data field d...

Cannot close the Customer/Vendor Relationships window
One user accidentally opened the Customer/Vendor Relationship in one of the forms in Sale module. She doesn't have access to add or delete a customer or vendor and this prevents her from closing the window. She tried the File->Close but the system is asking if she wanted to add the customer. If she clicks cancel it does not close the window, if she clicks Save it says that she doesn't have the proper security rights. Kindly advise how the user can close the window without using force logoff. Thank you. Yani, Click anywhere on the first line in the grid, then go to Edit &...

how do I get more than one sub-total in pivot tables?
I am using pivot tables to show summary HR recruiting data. The data columns are nested at three levels - priority(a, b or c), type(add/replace), number of reqs and number of positions. The pivot table automatically gives me sub-totals within the priority so I get number of reqs that are adds of priority A for example. I also automatically get a total of number of reqs and total of number of positions. What I'm trying to get is the sub-total of number of reqs that are adds regardless of priority. Move Type to the column area, and you'll get columns with totals for Add and...

merging table frames
I sent a similar message previously but it wasn't posted. I'm looking for a way to merge table frames. I downloaded from a website and when I copied the data to Publisher, it set the page into table frames. I want simple text all in one text box. I would like to either connect all those frames into one and then get rid of all the table formatting as I only want text. When I cut out the text in any of the frames, I am left with a grey page. I have looked through 3 books, gone to all the help screens and can find nothing about this. Help! Did you try "Paste Special"? -- Jo...

relationship
Hi, I'm not sure how to go about this. Can someone point me in the right direction? I have two tables. There are two fields in one table that will match two fields in the other table. When I change the CurrentRevision value in tblRevisions, I want matching record in tblOnTimeDelivery to change the CurrentRevision value also. Here's what I have: tblRevisions *JobNumber *CurrentRevision and other fields tblOnTimeDelivery *JobNumber *CurrentRevision and other fields Thanks for your help, Joel Joel From your description, I'm having a bit of t...

Fields from two Tables on Report.
Hi, My project is in MS Access 2002. In that I have one Form which enter data in two different Tables, when I click on Submit Button it will run two diffrent queries one by one. Now I want to generate Report from that Tables. But I don't know how can I put Fields from two different Tables in Report's Detail Section. If Feilds are from only one Table than I can set Report's Record Source property to that TableName.\ But I don't know how to set it for two tables? Even I put fields in Report and set there Control Source Property to that [TableName].[FieldName]. But when I open r...

Pivot Table % of
I have a pivot table like the followng: Joe Trans Ct A 5 B 3 C 2 Total Joe 10 Kelly A 6 B 1 C 4 Total Kelly 11 I added another Count of Trans shown as a "% of column", but it gives me the percent of the entire column when what I really want is the % of Total Joe, % of Total Kelly, etc. In this instance I simply made the names go across instead of down which solved the problem, but there are instances where I don't want to do that. Any suggestions for getting the percent per item rather than pe...

Weighted Average in Pivot Table
I have a pivot table based on the following data Region Team Assets Return North A 10 12 North B 20 4 North C 30 -5 South A 50 3 South C 25 5 South D 60 8 East A 4 9 East B 12 4 West A 100 6 West B 25 18 West C 3 4 West D 32 1 that looks like this Average of Return Region Total East 6.50 North 3.67 South 5.33 West 7.25 Grand Total 5.75 However, instead of simple average, I would like to see a weighted average, so for example, the weighted average of East would be 4/(4+12)* 9 + 12/(4+12)* 4 or 5.50 instead of the simple average of 6.50. Can I do this within a pivot tab...

Calculated fields in Pivot Tables
Is it possible to use an "if" formula when creating a calculated field? I have an existing field in my pivot table called commissions and I want to create a new field that will give me a 1 if for each row if commissions are over 4 and a 0 if they are under 4. Is this possible? What is the formula? It seems ok Try something like : =IF(Commissions>=4,1,0) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "lj" <lj@spu.edu> wrote in message news:1132008509.143327.194520@g47g2000cwa.googlegroups.com... > Is it possible to u...

Querying one-to-many relationships
I have two tables, one table with one primary key and one table with several keys: Table 1 NDC GPI 57866660701 1 Table 2 NDC APPL_NO APPL_TYPE_CD 57866660701 074105 2 57866660701 074140 2 57866660701 074207 2 57866660701 074410 2 57866660701 075927 2 57866660701 076494 2 There are a two things I need to do after joining these two tables. One is to find out if there are any records in the second table with different values in the "APPL_TYPE_CD" field for one NDC number. The other is to only return the first record from the second table, not all...

Kit Components in SOP and Inventory Tables
We have inventory items that we sell as a kit and as individual components. I use Access to query data from SQL tables and am able to get info quicker and more detailed than in Smart List. This works well for many of our sales and commission reports. In order to get accurate reports in the past by inv item # I query all line items that have an extended price > zero, this eliminates the kit components, but includes the kit item and the items we sell as individual components. The tables I am currently using are RM00101, SOP30200, SOP30300, IV00101 - where ITMGEDSC = "lens or len...

Combine / Join / Merge office tables of different sizes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm looking for an easy way to combine / join / merge two different word tables that are different sizes. It happens to me all the time -- the top table and the bottom table have the same number of columns, the sizes are close, but not exactly the same. When you delete the paragraph mark between the two, they merge, but not quite properly. There is a dark line between the two tables and you cant adjust properties for the combined table. I would just make the tables the same size before the combine, but often that is...

Cannot sort blanks to bottom of table
We are unable to properly perform and ascending sort (A-Z) when a table has blank cells in the sort column. The resulting table, after sorting A-Z, places all the rows with blanks in the sort column at the top of the table instead of at the bottom. How can we get the data to sort to the top of a table (using the drop-down menu for that column) with the rows (with a blank cell in the sort column) at the bottom, versus the top, of the list? We have tried filtering the blanks, but this hides rows which then obscure data we need to display to the right of the tables. As of today, we have...

Entity Customer Relationship
Hi CRMers, Under the Account Entity, there is Relationships. Clicking on Relationships will bring up the Customer Relationship Entiry. In that Entity, Role 1 and Role 2 are picklist with Campaign Partner and Referral. When I tried to edit this entity, Role 1 and Role 2 are of type lookup instead. Can anyone please advice how I can change the values of Role 1 and Role 2? Many thanks in advance. Cheers, Dixon To add new relationship role go to Settings->Relationship Roles and click the new button. You need to have atleast ReadRelationshipRole privilege to see the Relationship Roles ...

Pivot Chart/Table Tutorial
Does anyone know where I can find a good on-line tutorial or any kind of step-by-step for using pivot tables and charts? I'm a good Excel user, but I've never used pivots...I prefer data sort...but now I've got to start using pivots in Excel & Access. Any ideas? Thanks, Greta Greta - Debra Dalgleish has an introduction to the world of pivots here: http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm I'd suggest you learn all you can about pivot tables, one of Excel's best features, and use the data in regular charts. Pivot charts are a great id...

pivot table sorting
I want to rearrange my columns in a pivot table. does anyone know how to do this? thanks robert You can manually drag the selected item to its new position. Also, you can right click on an item in the column you want to change and select Field Settings..., click the Advanced button, then make a selection from the available radio buttons (like Ascending), then OK and OK. -- Toby Erkson Oregon, USA "robert" <rocksrus@stones.com> wrote in message news:%23C5T72fMEHA.620@TK2MSFTNGP10.phx.gbl... > I want to rearrange my columns in a pivot table. > does anyone know...

Updating an unbound table
I have a form that contains controls that are bound to an underlying table. I want to update a summary table when the update of a displayed record is complete. How can I tell when the update is complete, and can I tell what the contents of the updated record are? Am I making this harder that it really is? Thanks... If you are taking about the time it takes to write the record to the table and get it in memory, it should be immediate. There are occasionally what are called "slow writes" in Access if you are writing tons of data. But you should be able to see that ...

Better relationship
Hello Everybody, Let's say I want to link two columns in two different tables. Column from table1 is data source for the column in table2. Should I connect both columns directly? OR PrimaryKey of first table to the relevant column of second column? Which relationship would be better? Thank you, -- Syed Zeeshan Haider -------------------------------------------------------------------------------- Learn about famous muslim names at http://szh.20m.com/learn/aboutnames.html On Sun, 8 Apr 2007 19:31:41 -0500, "Syed Zeeshan Haider" <szhatforumsDELETE@hotpop.SPAMS.com&g...

Allow customization of relationship view columns in the account
When viewing the "Relationships" section for an account, we cannot change the columns displayed in customizing and are limited to Party 1 Role 1 Party 2 Role 2 which is very limiting. It would be usefult to be able to add additional columns to better descripe the relationships based on fields maintained in the account or contact record. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follo...