Question about fields, table relationships & sql

[moradisndat]

I have 3 tables. The field in red is the problem area.

tblTypes: (PK)TypeID, Type
tblAccounts: (PK)AcctID, (FK)TypeID, AcctName
tblJournalEntries: (PK)JournalID, Date, (?)TypeID, (FK)AcctID, Amount

screenprint uploaded here if it helps--> h t t p ://imgur.com/ktRdNNE

So my confusion is with tblJournalEntries. I don't think I have it set up correctly. I want to be able to select TypeID (expense, income, etc) and have AcctNames show only the matching TypeID.

SELECT [tblAccounts].[AcctID], [tblAccounts].[AcctName] FROM [tblAccounts] ORDER BY [AcctName]; shows all accounts regardless of type.

I tried using this but the field came up blank: SELECT DISTINCTROW [tblAccounts].[AcctID], [tblAccounts].[AcctName] FROM tblAccounts WHERE ((([tblAccounts].[TypeID])=[tblJournalEntries].[TypeID] ORDER BY [AcctName]));

Any help or suggestions would be much appreciated. Thanks.

 

[moradisndat]

Re: Question about fields, table relationships FOLLOWUP

OK, nevermind. I figured it out. (I think I just needed a break away from the computer.)

So to correct the problem, I deleted the field TypeID (in red) from tblJournalEntries since it would have been redundant data.

Also I realized that I shouldn't have fields in a table dependent on another field in the table so I made a form for JournalEntries that uses comboboxes for the fields TypeID and AcctName.

SELECT [Types].[TypeID], [Types].[Type] FROM [Types] ORDER BY [Type];
and
SELECT DISTINCTROW [Accounts].[AcctID], [Accounts].[AcctName] FROM Accounts WHERE ((([Accounts].[TypeID]) LIKE [Forms]![JournalEntries]![TypeID])) ORDER BY [AcctName];

and it worked!!! yay!

 

[Galaxiom]

Type is a reserved word and is best avoided.

https://support.office.com/en-in/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

 

[moradisndat]

Hi, thanks for the info about reserved words. I wasn't aware of that. I will change it to AcctType to avoid problems.