Solved

will Incorrectly setting relationships between two tables for a query not result in data enter

Posted on 2014-11-07
11
154 Views
Last Modified: 2014-12-02
Hello,

I am having a hard time setting my relationship bwtween my two tables to allow me to enter data in once I create a form from my query (based off the two tables)
0
Comment
Question by:Jass Saini
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40429845
Mostly likely these tables have parent/child relationship. Consider making a form where you can see (edit) parent fields. And then add a subform using the other table for viewing or edit.

Above will work assuming the tables are designed correctly.

Mike
0
 
LVL 84
ID: 40432971
I'd agree with Mike - create a Mainform/Subform setup, and go from there. While it's possible to create forms based on these types of queries, Edits/Adds/Deletes can be very tricky to get right.

If you are NOT using a query that has a Parent/Child relationship, then please show the SQL of that query, and explain who the two tables in the query are intended to interact (and how they're related).
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 333 total points
ID: 40433004
Relationships are not made in queries.  They are defined in the relationships window and should be enforced with RI.  In a query you can JOIN any two columns as long as the data types are compatible whether the join makes sense or not.  Typically in a query, your join would mimic the relationship defined for the two tables.

In a 1-many relationship (the most common), the join fields will be the primary key of the one-side table to a data field in the many-side table.  The data field may be part of a multi-column PK for the many-side table.
In a 1-1 relationship (uncommon), the join fields will be the primary key of one table to the primary key of the other table.  In this case, one of the tables will probably have an autonumber as the PK and that should be designated as the "parent".  The other table will have a long integer as the PK.
0
 

Author Comment

by:Jass Saini
ID: 40453404
Hello,

Unfortunately I was given a form to work with as well....Just cleaning up other peoples messes..

Here is my query:

SELECT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.CostCen, Final_Table.[Fund:], Final_Table.BC1_Change, Final_Table.TotalBC1, Final_Table.BC2_Change, Final_Table.TotalBC2, Final_Table.BC3_Change, Final_Table.TotalBC3, Final_Table.BC4_Change, Final_Table.TotalBC4
FROM Final_Table, dbo_tblOrgLook_master
WHERE (((dbo_tblOrgLook_master.Analyst) In ('D.Heitz')) AND ((dbo_tblOrgLook_master.Org) In ('5340')) AND ((dbo_tblOrgLook_master.CostCenter) In ('0560000000')) AND ((dbo_tblOrgLook_master.Fund) In ('15G0010000')) AND ((dbo_tblOrgLook_master.PEC) In ('2560000')));


I don't understand the "the data field may be part of a multi-column PK for the many-side table"?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 40453957
Your tables are not Joined ... a query with a Join would look something like this:

SELECT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.CostCen, Final_Table.[Fund:], Final_Table.BC1_Change, Final_Table.TotalBC1, Final_Table.BC2_Change, Final_Table.TotalBC2, Final_Table.BC3_Change, Final_Table.TotalBC3, Final_Table.BC4_Change, Final_Table.TotalBC4
FROM Final_Table JOIN dbo_tblOrgLook_master AS olm ON Final_Table.SomeField=olm.SomeRelatedField
WHERE (((dbo_tblOrgLook_master.Analyst) In ('D.Heitz')) AND ((dbo_tblOrgLook_master.Org) In ('5340')) AND ((dbo_tblOrgLook_master.CostCenter) In ('0560000000')) AND ((dbo_tblOrgLook_master.Fund) In ('15G0010000')) AND ((dbo_tblOrgLook_master.PEC) In ('2560000')));

In other words, you "tell" Access which Field (or Fields) are the common links between those two tables, and Access may then allow you to update the query. Unless you can create that Join, your query won't be updateable.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 84
ID: 40453960
And again - if you can explain how those two tables are intended to interact, and how they're related, we might be able to give you more guidance.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 333 total points
ID: 40455160
An un-specified join as you had in your example creates a Cartesian Product where all rows of tblA are matched to all rows in tblB and the resulting recordset is not updateable.  Changing the join as Scott suggested to be specific will probably make the query updateable assuming he guessed correctly on how the tables should be joined.
0
 

Author Comment

by:Jass Saini
ID: 40476576
Here is my new query..and now I am getting an error.  Syntax error in FROM clause

SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Org Name:], Final_Table.CostCen, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1Change], Final_Table.[TotalBC1], Final_Table.[BC2Change], Final_Table.[TotalBC2], Final_Table.[BC3Change], Final_Table.[TotalBC3], Final_Table.[BC4Change], Final_Table.[TotalBC4]
FROM Final_Table JOIN dbo_tblOrgLook_master  AS olm ON Final_Table.PEC = dbo_tblOrgLook_master.PEC
WHERE dbo_tblOrgLook_master.Analyst IN('P.Subia') AND dbo_tblOrgLook_master.Org IN('4424') AND dbo_tblOrgLook_master.CostCenter IN('1160000000') AND dbo_tblOrgLook_master.Fund IN('15G5010000') AND dbo_tblOrgLook_master.PEC IN('5020090');
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40476921
I'll take one stab at fixing the problems.  If my suggestions don't help, please start a new question.
1.  JOIN should be INNER JOIN
2. Once you assign an alias, you MUST use it so any place in the select or where clause where you have dbo_tblOrgLook_master, it MUST be changed to olm

Also, why are you using In(..) rather than =?  It may not make any difference to the query engine but if it does, the difference will be negative rather than positive so unless you have multiple values, go with the simpler expression.

And finally - use the QBE to build your queries.  It would have found both of the errors and not allowed you to save the query.
0
 

Author Comment

by:Jass Saini
ID: 40476962
Hello,

Can I use two SameRelatedFields in the FROM clause?  I am using In rather than = is because the selection is coming from my first form.  I can attach my DB if you like??
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40477064
If the two errors I pointed out didn't resolve the problem, please start a new question.

The In() suggestion was a helpful hint.  It has nothing to do with the problem.  If you are building the string on the fly, then don't make any changes if your code is set to allow multiple values.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Print focus keeps focusing to the caller form 3 25
Format vertical text in Access 2016 3 30
Track name AutoCorrect info 14 46
MS Access Calculation wont work 5 35
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now