adding a record in a query with relationships difined only in query

[Stephen]

Hi all.. need more help please. fourth posting... I'm looking for any ideas. Thanks

John identified my issue, which is described below.. but I’m not sure if I can resolve this, as I’m unable to define relationships in the main database. This is because I have a procedure that imports records from a txt file, but the import itself violates relationships. Thus if I have the relationships linked the import fails with errors. Once the import is finished the database then adds the required fields in the related tables

The relationships in the queries are linked, but do not provide the same link as would the relation one to many in main database. Why can I not get the same multikey link in queries, when I can in the relationships screen

The TblHistStaticData (Many) and TblUsers (one) have two primary keys that are linked separately both use the relationship; ‘only include rows where the joined fields from both tables are equal’. The table TblUsers (Many) and TblDesk (one) is joined by one foreign key in each table

Is there a way to get around this without setting up relationships in the main database?

Thanks again for your help!

**...Followng taken from a previous posting..******.......

What are the relationships between the three tables? A query will be updatable only if there's a clear one-to-many chain on Primary (or unique) keys linked to foreign keys. For example, this should be updatable if the relationships are

tblDesks 1->M tblUsers 1->M tblHistStatDat

.... but it won't be updatable if the relationships are

tblDesks M<-1 tblUsers 1->M tblHistStatDat

John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com

(Microsoft Access MVP since 1993

I have a query that that takes data from three tables. These tables have been linked inside the query. My problem is that when the query is run, it will not enable users to add additional records to the main table (tblHistStatData). see below for sql

SELECT tblHistStatData.Unit, tblHistStatData.UserID, tblHistStatData.RepoDate, tblHistStatData.PayRecZ, tblHistStatData.PayRecNotZ, tblHistStatData.Deferred, tblHistStatData.DMIUpdate, tblHistStatData.TradeSupport, tblHistStatData.VerifyAccept, tblHistStatData.VerifyReject, tblHistStatData.AuthorizeAccept, tblHistStatData.AuthorizeReject, tblHistStatData.DSSInput, tblHistStatData.CLLInput,tblHistStatData.MovementCutOff, tblHistStatData.ConfirmationMatch

tblHistStatData.UserTotal, tblHistStatData.CreateTime, tblHistStatData.SourceType, tblHistStatData.errors, tblHistStatData.SWIFTS, tblHistStatData.MEPS, tblHistStatData.ARIES, tblUsers.GroupName, TblDesks.Cod

FROM tblHistStatData INNER JOIN (tblUsers INNER JOIN TblDesks ON tblUsers.GroupName = TblDesks.DeskName) ON (tblHistStatData.UserID = tblUsers.UserID) AND (tblHistStatData.Unit = tblUsers.Unit

WHERE (((tblHistStatData.RepoDate) Between #1/1/2004# And #2/9/2004#) AND ((TblDesks.Code) Like [Please enter desk officers initials] & "*"))

 

[Tom Ellison]

Dear Stephen:

The inability to define (and enforce) relationships in the "main
database" is likely to be a crippling problem. I suggest you must
resolve this issue in the data before you attempt to add the imported
rows to the main table. The expectation that you will have usable
data without referential integrity would generally be an illusion.

So, in what way(s) does the imported data violate the expected
referential integrity? How do you propose to fix it, either
automatically or with manual intervention?

It sounds like you are aiming to have a form in which users can add
rows to the tables. Since you are dealing in one-to-many
relationships, you should use a separate subform for each table. It
really makes no sense to expect to add a row to three separate tables
on one form.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi all.. need more help please. fourth posting... I'm looking for any ideas. Thanks.

John identified my issue, which is described below.. but I’m not sure if I can resolve this, as I’m unable to define relationships in the main database. This is because I have a procedure that imports records from a txt file, but the import itself violates relationships. Thus if I have the relationships linked the import fails with errors. Once the import is finished the database then adds the required fields in the related tables.

The relationships in the queries are linked, but do not provide the same link as would the relation one to many in main database. Why can I not get the same multikey link in queries, when I can in the relationships screen?

The TblHistStaticData (Many) and TblUsers (one) have two primary keys that are linked separately both use the relationship; ‘only include rows where the joined fields from both tables are equal’. The table TblUsers (Many) and TblDesk (one) is joined by one foreign key in each table.

Is there a way to get around this without setting up relationships in the main database?

Thanks again for your help!.

**...Followng taken from a previous posting..******........

What are the relationships between the three tables? A query will be updatable only if there's a clear one-to-many chain on Primary (or unique) keys linked to foreign keys. For example, this should be updatable if the relationships are:

tblDesks 1->M tblUsers 1->M tblHistStatData

... but it won't be updatable if the relationships are:

tblDesks M<-1 tblUsers 1->M tblHistStatData


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/

(Microsoft Access MVP since 1993)

I have a query that that takes data from three tables. These tables have been linked inside the query. My problem is that when the query is run, it will not enable users to add additional records to the main table (tblHistStatData). see below for sql.

SELECT tblHistStatData.Unit, tblHistStatData.UserID, tblHistStatData.RepoDate, tblHistStatData.PayRecZ, tblHistStatData.PayRecNotZ, tblHistStatData.Deferred, tblHistStatData.DMIUpdate, tblHistStatData.TradeSupport, tblHistStatData.VerifyAccept, tblHistStatData.VerifyReject, tblHistStatData.AuthorizeAccept, tblHistStatData.AuthorizeReject, tblHistStatData.DSSInput, tblHistStatData.CLLInput,tblHistStatData.MovementCutOff, tblHistStatData.ConfirmationMatch,

tblHistStatData.UserTotal, tblHistStatData.CreateTime, tblHistStatData.SourceType, tblHistStatData.errors, tblHistStatData.SWIFTS, tblHistStatData.MEPS, tblHistStatData.ARIES, tblUsers.GroupName, TblDesks.Code

FROM tblHistStatData INNER JOIN (tblUsers INNER JOIN TblDesks ON tblUsers.GroupName = TblDesks.DeskName) ON (tblHistStatData.UserID = tblUsers.UserID) AND (tblHistStatData.Unit = tblUsers.Unit)

WHERE (((tblHistStatData.RepoDate) Between #1/1/2004# And #2/9/2004#) AND ((TblDesks.Code) Like [Please enter desk officers initials] & "*"));