Re: Query for Subform - G/L Account Transactions (Access XP)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for that info. It helped lead me to the solution to my problem.

It's not feasible at this point to redesign the tables as that would require
a total redesign of the application. However, by following your design, I
used the IIF() function to create the following query that does what I want:

SELECT DISTINCT tblGeneralLedger.Company, tblGeneralLedger.GLID,
tblGLDebits.SystemID, tblGLCredits.SystemID, tblGeneralLedger.GLDate,
tblGeneralLedger.Journal, tblGeneralLedger.GLDescription,
[forms]![frmAccounts]![txtAccountID] AS Account,
IIf([tblGLDebits].[DebitAccount]=[forms]![frmAccounts]![txtAccountID],
[tblGLDebits].[DebitAmount],"") AS Debits,
IIf([tblGLCredits].[CreditAccount]=[forms]![frmAccounts]![txtAccountID],
[tblGLCredits].[CreditAmount],"") AS Credits
FROM (tblGeneralLedger INNER JOIN tblGLDebits ON tblGeneralLedger.GLID =
tblGLDebits.SystemID) INNER JOIN tblGLCredits ON tblGeneralLedger.GLID =
tblGLCredits.SystemID
WHERE (((tblGeneralLedger.Company)=[forms]![fmnuMainMenu]![cboCompany]) AND
((tblGLDebits.SystemID)=[tblGeneralLedger].[GLID]) AND
((tblGLCredits.SystemID)=[tblGeneralLedger].[GLID]) AND
((tblGLCredits.CreditAccount)=[forms]![frmAccounts]![txtAccountID])) OR
(((tblGLDebits.DebitAccount)=[forms]![frmAccounts]![txtAccountID]));

I then used this query as a record source for a subform to display the
results on my tab control. One problem I'm having, though, is that the
amounts display with auto formatting of the decimals (e.g. 180 and 22.9 and
34.98 instead of 180.00 and 22.90 and 34.98). I tried setting the decimal
places to "2" and the format to "Currency" in the controls on the subform,
but that didn't change anything.

Can you tell me how to fix this?

Thanks, again, for pointing me in the right direction...

Regards, Chris

"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your set up is called attribute splitting: putting attributes of one
record into more than one table. You only need one table for the G/L:

CREATE TABLE GeneralLedger (
GLID COUNTER NOT NULL , -- a Counter is an AutoNumber
xact_date DATETIME NOT NULL, -- transaction date
gl_desc TEXT (25) NOT NULL,
gl_ref TEXT (10) NOT NULL,
xact_type TEXT (1) NOT NULL , -- it holds "C," credit or "D," debit.
xact_amt DOUBLE NOT NULL, -- transaction amount
CONSTRAINT PK_GL PRIMARY KEY (xact_date, gl_desc, gl_ref, xact_type)
)

[The above is a data definition language (DDL) command that will create
the table if you run it from a query object (place it in the SQL view.
Get rid of the comments and their leading "--" characters, first.)]

The gl_desc and the gl_ref probably should be Foreign Keys to tables
that hold acceptable descriptions and references numbers.

This system uses positive numbers for both Debit and Credit
transactions.

The Primary Key is iffey 'cuz you may have more than one transaction on
the same date that has the same description, reference, and transaction
type! The GLID is not a good primary key 'cuz it won't prevent
duplicate entries (the main reason for primary keys).

Now you have a pretty easy query to get the data you want:

SELECT GLID, gl_desc, gl_ref,
IIf(xact_type = "C", xact_amt) As DebitAmount,
IIf(xact_type = "D", xact_amt) As CreditAmount
FROM GeneralLedger

This won't really work as a RecordSource for a sub-form, 'cuz of the
IIf() functions. For the Sub-form I'd recommend just having the table
as a RecordSource. Then put the fields: GLID, gl_desc, gl_ref,
xact_type, xact_amt on the form.

Allow the user to select the description, reference, and transaction
type from ComboBoxes. This is where having a separate table of
descriptions and references comes in handy - you'd use them as the
ComboBox's RowSource. The xact_type would just be a ComboBox with a
RowSourceType of Value List and a RowSource of "C", "D".

Remember to set up the xact_amt field so it only accepts positive
numbers.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMJJdoechKqOuFEgEQJVsACfYBC5L4Y9r/eSEnfxRQuswccRVzYAoOLj
oDC+JaVEUHMHx8hjI87dKp/N
=+Kau
-----END PGP SIGNATURE-----


Chris O''Neill wrote:
I have a form called frmAccounts that shows information about each financial
account in my chart of accounts. This form has several tabs (details,
accounting rules, notes, etc) that are all tied together by the account
number (tblAccounts.AccountID).

I want to add a tab that shows all G/L transactions for the account, again,
linked to the other tabs using the account number. Here's the table
structure of my G/L

tblGLHeader:
---------------
GLID Unique ID (autonumber)
Date Transaction date (date)
Desc Transaction description (text)
Ref Transaction source journal reference (text)

tblGLDebits:
--------------
GLDRID Unique ID (autonumber)
SysID Same as GLID (long integer)
DRAcct Debit account (text)
DRAmt Debit amount (currency)

tblGLCredits:
---------------
GLCRID Unique ID (autonumber)
SysID Same as GLID (long integer)
CRAcct Credit account (text)
CRAmt Credit amount (currency)

I want to create a subform that will be placed on the tab that shows the
transactions. The subform will be a datasheet with these fields:

GLID (from tblGLHeader)
Description (from tblGLHeader)
Reference (from tblGLHeader)
DebitAmount (from tblGLDebits)
CreditAmount (from tblGLCredits)

A sample line from account 1100 (Bank) might look like this:

GLID Description Reference DebitAmount
CreditAmount
-----------------------------------------------------------------------------------------
10 Rent EXPN20 $0.00
$200.00
15 Deposit . INCM30 $100.00
$0.00

Can anyone help me with the SQL query to use to create this subform? Any
help would be greatly appreciated as SQL is not one of my strong points.

Regards, Chris



.

Relevant Pages

  • Re: Query for Subform - G/L Account Transactions (Access XP)
    ... GLID COUNTER NOT NULL, -- a Counter is an AutoNumber ... xact_date DATETIME NOT NULL, -- transaction date ... Allow the user to select the description, reference, and transaction ... This form has several tabs (details, accounting rules, notes, etc) that are all tied together by the account number. ...
    (microsoft.public.access.queries)
  • Re: Word Merge for an invoice
    ... it just for the creater of the merge document? ... It can certainly handle Account Number, Name and Address and in your case, ... The One is not a reference to a side in any event. ... Account Number Name Address Transaction date Transaction ...
    (microsoft.public.word.mailmerge.fields)
  • Re: New Method for Authenticated Public Key Exchange without Digital Certificates
    ... have the account disabled. ... then the next time you do a financial transaction ... ... eventually arrives at your financial institution ... ... in order to do credit card transactions, the merchant has to ...
    (sci.crypt)
  • Re: ComboBox Problem... Please, help me out!
    ... and tblTransDtl (transaction detail). ... You also have tables for SheetRegister and Account. ... Every sheet belongs to an only single ...
    (microsoft.public.access.formscoding)
  • Re: Can extra processing threads help in this case?
    ... an HTTP acknowledgement. ... Instead of crediting the transaction if the operation fails, ... reliability. ... of 10 from that account, ...
    (microsoft.public.vc.mfc)