Bob- SELECT tblJournal.*, tblJ2.* FROM tblJournal INNER JOIN tblJournal As tblJ2 ON tblJournal.[Date] = tblJ2.[Date] AND tblJournal.[Journal Entry] = tblJ2.[Journal Entry] WHERE tblJournal.Amount = -tblJ2.Amount
Use a similar technique to join the table to itself to solve your other problem. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Kate Aliesan Sent: Monday, November 21, 2005 9:29 AM To: [email protected] Subject: [ms_access] Query problems Background: I review databases which have accounting data in it. I use Access 2000. The way that accounting data works is that for each account that there is an entry for $1000, there has to be another account(S) that there is an entry for -$1000 on the same date using the entry or source. For example, on 6/04/2005, using journal entry ab200, an entry could look like : Account Amount Date Journal Entry 1090 1000 06/04/2005 ab200 5222 1000 06/04/2005 ab200 I would not know the account numbers or date. The first question is how do I design a query that would tell me the accounts on a given date with the same journal entry that have the same absolute value but on is a positive and the second is a negative. The second question pertains to the same data. I want to be able to design a query that occurs on the same day between two or more accounts for opposite amounts. For example: I would want to know all journal entries that occurred on the same day inwhich there is a positive to account 5252 and a negative to account 6000 or a negative to account 6000 through 6050. Any help would be greatly appreciated Bob --------------------------------- Yahoo! FareChase - Search multiple travel sites in one click. [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/u8TY5A/tzNLAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/u8TY5A/tzNLAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
