Jass Saini
asked on
will Incorrectly setting relationships between two tables for a query not result in data enter
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)
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)
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).
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Anal yst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgN ame, dbo_tblOrgLook_master.Cost Center, dbo_tblOrgLook_master.Fund , dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.Prog ramName, 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.A nalyst) In ('D.Heitz')) AND ((dbo_tblOrgLook_master.Or g) In ('5340')) AND ((dbo_tblOrgLook_master.Co stCenter) In ('0560000000')) AND ((dbo_tblOrgLook_master.Fu nd) In ('15G0010000')) AND ((dbo_tblOrgLook_master.PE C) In ('2560000')));
I don't understand the "the data field may be part of a multi-column PK for the many-side table"?
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.Anal
FROM Final_Table, dbo_tblOrgLook_master
WHERE (((dbo_tblOrgLook_master.A
I don't understand the "the data field may be part of a multi-column PK for the many-side table"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Anal yst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgN ame, dbo_tblOrgLook_master.Cost Center, dbo_tblOrgLook_master.Fund , dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.Prog ramName, 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.Anal yst IN('P.Subia') AND dbo_tblOrgLook_master.Org IN('4424') AND dbo_tblOrgLook_master.Cost Center IN('1160000000') AND dbo_tblOrgLook_master.Fund IN('15G5010000') AND dbo_tblOrgLook_master.PEC IN('5020090');
SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Anal
FROM Final_Table JOIN dbo_tblOrgLook_master AS olm ON Final_Table.PEC = dbo_tblOrgLook_master.PEC
WHERE dbo_tblOrgLook_master.Anal
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.
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.
ASKER
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??
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??
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.
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.
Above will work assuming the tables are designed correctly.
Mike