Excel Vlookup (kind of) in Access

[Rubble]

What I have is a master account index in one table that refers to all of the
actual account numbers in another column in the table. In another table in
one column I have an account index that relates to asset account, in another
column I have an account index that relates to accum. depr account, in a
third column I have an account index that relates to depr expense,
4th...5th...6th... -- I want to have a query that replaces the account index
with the actual account number. If I were only doing one account index to
account number it would be easy; however, I find it difficult to do multiple
columns unless I make copies of the original table 6 times over so each
column I want to have an actual account number I can have a separate
relationship defined.

 

[George Nicholson]

In another table in

one column I have an account index that relates to asset account, in
another
column I have an account index that relates to accum. depr account, in a
third column I have an account index that relates to depr expense,
4th...5th...6th...

well, I'd rebuild this table. It violates relational db normalization rules
- Account index
- Account Type (asset, accum depr, depr expense, etc.)

Maybe even add Account Num and use this as your Master cross-ref?

Once set up this way you can use a crosstab query to *view* your account
numbers or indexes in separate asset, depr, etc. columns, but don't store
data that way, it'll drive you absolutely nuts trying work with it (as you
have discovered).

HTH,

 

[Rubble]

Thank you -- actually the original tables were created by MS as part of Great
Plains -- the reason (I think) it violates normalization rules is it is going
between a sub ledger and general ledger so one table in the sub ledger holds
all of the acct indexes for each of the types of events -- it is kind of a
pain though -- I just want to kick out a report that shows all of the active
assets and their respective NBV's at whatever point in time depreciation has
been run through on the sub g/l. As part of the report I would like to
present the actual asset account number, accum depr account number, and depr
expense account number for each asset along with the financial data -- I
think for speed purposes I will only present asset account number.

Thanks again --

 

[THD69]

hi, i have two fields in a table like Cost & Rate. i need to formulate the
following calculation. i want to calculate A, B and 4.88 which is cumulative
depreciation on 3rd month. can i do it in a query by expression building or i
need write program/module...pls help.

Cost Rate A = (Cost*Rate) B = (Cost - A)
10 0.2 2 8
8 0.2 1.6 6.4
6.4 0.2 1.28 5.12

 

[MGFoster]

hi, i have two fields in a table like Cost & Rate. i need to formulate the
following calculation. i want to calculate A, B and 4.88 which is cumulative
depreciation on 3rd month. can i do it in a query by expression building or i
need write program/module...pls help.

Cost Rate A = (Cost*Rate) B = (Cost - A)
10 0.2 2 8
8 0.2 1.6 6.4
6.4 0.2 1.28 5.12

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

In a query like this:

PARAMETERS [Start Date] Date, [End Date] Date;
SELECT Cost, Rate, SUM(Cost*Rate) As A, SUM(Cost-(Cost*Rate)) As B
FROM table_name
WHERE date_column_name BETWEEN [Start Date] And [End Date]
GROUP BY Cost, Rate

HTH,
--
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/AwUBSuFFJYechKqOuFEgEQJr4wCgndHUZL3s6LD0lzQoBJiSNc3ajcQAn3d1
qRsO9lTnyLIsKQKEQbCibt09
=1nI+
-----END PGP SIGNATURE-----