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

  • Thread starter Thread starter Chris O''Neill
  • Start date Start date
C

Chris O''Neill

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
CreditAmoun
-----------------------------------------------------------------------------------------
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
 
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 said:
-----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-----

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
Click to expand...
Click to expand...
 
MGFoster said:
You can use something like this:

#,#.00;(#,#.00)

in the Format property field of the Text Box. It will show the .00
whenever the user doesn't enter ".00" in the Text Box and complete
partial decimals (.9 becomes .90). The parentheses around the 2nd
format puts parentheses around negative numbers. For more info on
different formatting options place the cursor in the Format property
field and hit the F1 key.
Click to expand...

Unfortunately, that didn't work. I put it in the Format field in the query,
as well as the Format field for the text box, but I'm still getting 3.9
instead of 3.90 and 40 instead of 40.00 when I display the subform. Could
have something to do with the fact that I'm displaying the subform in
datasheet view?

Again, any help and suggestions you can provide will be greatly appreciated.

Regards, Chris
 
cough why dont you just set the decimal properity in the table for
that field to 2 rather than auto

hope this helps

Regards
Kelvan
 
Lord Kelvan said:
cough why dont you just set the decimal properity in the table for
that field to 2 rather than auto
Click to expand...

I'm at my "regular" job so I can't check, but I'm 99.99% sure the fields are
set to Currency type with "2" as the decimal setting. (If *that's* the
problem, I'm gonna be sooooooooo embarrassed!) <Grin!>

Regards, Chris
 
Lord Kelvan said:
cough why dont you just set the decimal properity in the table for
that field to 2 rather than auto
Click to expand...

I check, and the tables are set to "Currency" format with "2" as the decimal
property. I've also set the format in the query and the form, but I'm still
getting the "auto" formatting
hope this helps
Click to expand...

Unfortunately, no, this didn't help. Any other suggestions or help would be
greatly appreciated.

Regards, Chris
 
umm you know in a text box on the form it also has a decimal places
properity which is set to default of auto you could try setting that
to 2

Regards
Kelvan
 
I guess I wasn't clear in my previous post.... Sorry!

The fields in the underlying table, the fields in the query, and the text
box controls on the form are all set to Currency format with 2 decimal
places. It's still displaying auto formatted numbers of varying decimal
places. Frankly, I'm stumped. :(

Any other suggestions?

Regards, Chris
 
.... try removing the currency format to be honest you should just use
decimal and if you want to display a $ then just do it in a label next
to the text box that could be causing it because if it is set to 2
decimal and you have set up the format thing then it is something else.
 
Back
Top