Linked Tables and Query Design Question

[burrina]

If I have the relationships right in the relationship window, when I go to create a query and use the 2 tables linked, it links them together and the query should work correct? This is NOT the case right now. Query wont work right. The only other common field they have is bank which is in both tables as well. bankid is also in both tables and why I chose it. Only other option is to use RegID ??? I need info out of the TBanks table to be used in the query for the check registry. Error it causes when I add the TBanks tables is a field becomes uneditable.




Query for the check registry that uses table TReg is a union query that adds a 3rd table into play.

Table TReg


1. RegID Primary Key
2. bankid number
3. bank text

Table TBanks

1.bankid Primary Key
2. Bank text


Table TReg with a primary key of RegID and table TBanks with a primary key of bankid . TReg is used for a check registry and TBanks is used for bank info.
Right now I have them linked by bankid on a one to many relationship.

[ssanfu]

...and the query should work correct? This is NOT the case right now. Query wont work right.

Can't say.... You didn't post the SQL of the query or post what the error message is. What do you mean "doesn't "work right"?

Remove "3. bank text" from table TReg.
Because the two tables are linked (related) by "bankID", the bank text is available.

[burrina]

I cant remove that field, it is used in a append query for my check register. I am using a complex scheduler to schedule events and then have them entered into my check register and so you wont have the same fields, names, etc.. in the tables and hence the needed like field names in both tables. Here is the sql of the query being used for the check register.


SELECT IIf([Void]=2,[debit]) AS ExpCLr, IIf(([Void]=1),[debit],0) AS ExpNotCLr, IIf(([Void]=3),[debit],0) AS ExpVoided, QRegA.debit, ConvertCurrencyToEnglish([debit]) AS ExpDebit, QRegA.credit, QRegA.RegID, QRegA.Amount, QRegA.BalDate, QRegA.ClrBank, QRegA.Account, QRegA.Void, Switch([Void]=1,"",[Void]=2,"Cleared Bank",[Void]=3,"Voided") AS ExpVoid, QRegA.ChkNo, QRegA.Memo, QRegA.Payee, QRegA.MYDate, QRegA.RegisterDte, QRegA.frequency, QRegA.frequencyamount, QRegA.scheduleddte, QRegA.freqpayee, QRegA.TransType, QRegA.PeriodTypeID, QRegA.EventStart, QRegA.Employee, QRegA.Bank, QRegA.BankID, QRegA.EventID
FROM QRegA
ORDER BY QRegA.BalDate, QRegA.Bank;



Error occurs when adding the TBanks table to the query above. It shows up on the form when you try and use it.

[ssanfu]

uses table TReg is a union query that adds

I missed this the first time I read it. Union Queries are not up-datable. You will have to accomplish this using a different method ....

RE: field named "Bank" - two tables have a field named the same but hold different data??
Don't know your structure/system, but that is bound to cause headaches

[burrina]

Thanks, I will figure it out. It is because you don't understand how the db is structured is why! Maybe you can't get there from here, but I bet you can from there.

Thanks,


Just thought I would let all know I did a workaround by creating a new query,report and some code work arounds. Lesson Learned.

When it's broke, use something else instead of trying to fix it !