Calling complex Access queries from within Excel

[Travis]

I've got a number of nifty queries in Access which I'd like to be able
to view the results of in Excel.

I don't think I can recreate the queries in Excel using MS Query, as
far as I can tell (and please correct me if I'm wrong) you can't do
such things as sum queries and other arithmetic operations.

Is there a way from Excel (some light VBA is ok, but I'm not proficient
yet) to call complex queries from Access, or should I use maketable
queries in Access to make a table with the data which I can then import
into Excel using the simple query builder available in Excel?

And while I'm at it, I'm still stumped by how to construct a query that
will put information from a portfolios table into the format I want.

The raw data in the table looks like this:

Investment1 Owner1 100% 100
Investment1 Owner1 50% 300
Investment1 Owner2 50% 300
Investment2 Owner2 100% 100
Investment2 Owner2 100% 1000
Investment3 Owner3 50% 20
Investment4 Owner4 100% 1

In the above table there are four investments owned in various
proportions by four different owners.

Investment1 has two parcels, the first parcel was bought in the name of
Owner1 and the second bought 50/50 with Owner2, the first parcel is 100
units, the secon parcel is 300 units (owned 50/50, e.g. 300 units owned
by two people. Note that this is not legally the same as owning 150
each as the investment could be a jointly held asset, not tenants in
common.

Investment2 was bought in two parcels, both solely by Owner2, there are
1100 units altogether.

Investment3 was bought in a single parcel, it is half owned by Owner3
and the rest by some outside party that I'm not bothering to track. I
only care that Owner3 has a 50% share in Investment3, bought in a
single transaction. There are 20 units, 50% owned.

Investment4 was bought in a single parcel, 100% of 1 unit by Owner4

The output I would like would be a table formatted this

<name of investment> <total number of units owned> <ownership shares>
Investment1 400 62.5% Owner1 37.5% Owner2
Investment2 1100 100% Owner2
Investment3 20 50% Owner3
Investment4 1 100% Owner4

There are of course other fields involved, like the dollar value of the
units and hence the beneficial $ values owned by each owner, but adding
these won't make the example any clearer to the newsgroup so I'm
omitting them. If I know how to do the above, I'll know how to do the
rest because I imagine its similar.

Any held would be greatly appreciated!

Thanks.

Travis

 

[Vincent Johns]

I've got a number of nifty queries in Access which I'd like to be able
to view the results of in Excel.

Have you tried using (in Excel) Data --> Get External Data --> New
Database Query?

I don't think I can recreate the queries in Excel using MS Query, as
far as I can tell (and please correct me if I'm wrong) you can't do
such things as sum queries and other arithmetic operations.

I'm not sure what you mean -- of course Excel does a good job of
calculating sums and doing other forms of arithmetic.

Is there a way from Excel (some light VBA is ok, but I'm not proficient
yet) to call complex queries from Access,
Yes.

or should I use maketable
queries in Access to make a table with the data which I can then import
into Excel using the simple query builder available in Excel?

You can do that, too, but I think it's unnecessary.

And while I'm at it, I'm still stumped by how to construct a query that
will put information from a portfolios table into the format I want.

The raw data in the table looks like this:

Investment1 Owner1 100% 100
Investment1 Owner1 50% 300
Investment1 Owner2 50% 300
Investment2 Owner2 100% 100
Investment2 Owner2 100% 1000
Investment3 Owner3 50% 20
Investment4 Owner4 100% 1

In the above table there are four investments owned in various
proportions by four different owners.

Investment1 has two parcels, the first parcel was bought in the name of
Owner1 and the second bought 50/50 with Owner2, the first parcel is 100
units, the secon parcel is 300 units (owned 50/50, e.g. 300 units owned
by two people. Note that this is not legally the same as owning 150
each as the investment could be a jointly held asset, not tenants in
common.

Investment2 was bought in two parcels, both solely by Owner2, there are
1100 units altogether.

Investment3 was bought in a single parcel, it is half owned by Owner3
and the rest by some outside party that I'm not bothering to track. I
only care that Owner3 has a 50% share in Investment3, bought in a
single transaction. There are 20 units, 50% owned.

Investment4 was bought in a single parcel, 100% of 1 unit by Owner4

The output I would like would be a table formatted this

<name of investment> <total number of units owned> <ownership shares>
Investment1 400 62.5% Owner1 37.5% Owner2
Investment2 1100 100% Owner2
Investment3 20 50% Owner3
Investment4 1 100% Owner4

There are of course other fields involved, like the dollar value of the
units and hence the beneficial $ values owned by each owner, but adding
these won't make the example any clearer to the newsgroup so I'm
omitting them. If I know how to do the above, I'll know how to do the
rest because I imagine its similar.

I imagine so, too.

Any held would be greatly appreciated!

Thanks.

Travis

OK, suppose we start with this Table:

[Portfolio] Table Datasheet View:

Name Owner Share Units
---- ----- ----- -----
Investment1 Owner1 1 100
Investment1 Owner1 0.5 300
Investment1 Owner2 0.5 300
Investment2 Owner2 1 100
Investment2 Owner2 1 1000
Investment3 Owner3 0.5 20
Investment4 Owner4 1 1
Investment3 -- 0.5 20

I added the last record. (Yes, I /know/ that this makes the Table
unnormalized, since the value could be calculated; I just figured it
would be easier for now to add a dummy record for external investors.
If this happens a lot, it should be done via a Query. If you want to do
this via a Query and need help doing it, post another message.)

What follows involves several Queries. There may be better ways to
accomplish the task, but I think that breaking it out this way makes all
the steps easy to understand and to modify as desired.


I define a Query to list the total units in each investment.

[Q_01SharesByInvestment] SQL:

SELECT Q_02SharesByParcel.Name,
Sum(Q_02SharesByParcel.UnitsEffective) AS TotalUnits
FROM Q_02SharesByParcel
GROUP BY Q_02SharesByParcel.Name
ORDER BY Q_02SharesByParcel.Name;

[Q_01SharesByInvestment] Query Datasheet View:

Name TotalUnits
---- ----------
Investment1 400
Investment2 1100
Investment3 20
Investment4 1


We calculate the equivalent number of shares each owner has, ignoring
the distinction between full ownership of some shares and part ownership
of all shares.

[Q_02SharesByParcel] SQL:

SELECT Portfolio.Name, Portfolio.Units,
Portfolio.Share, Portfoliwner,
[Portfolio]![Units]*[Portfolio]![Share] AS UnitsEffective
FROM Portfolio
ORDER BY Portfolio.Name, Portfoliwner;

[Q_02SharesByParcel] Query Datasheet View:

Name Units Share Owner UnitsEffective
---- ----- ----- ----- --------------
Investment1 300 0.5 Owner1 150
Investment1 100 1 Owner1 100
Investment1 300 0.5 Owner2 150
Investment2 1000 1 Owner2 1000
Investment2 100 1 Owner2 100
Investment3 20 0.5 -- 10
Investment3 20 0.5 Owner3 10
Investment4 1 1 Owner4 1


We calculate the percentages to be reported in the final list. Dummy
owner "--" gets ignored from here on.

[Q_03SharesByOwner] SQL:

SELECT Q_02SharesByParcel.Name,
Q_02SharesByParcel.Owner,
Q_01SharesByInvestment.TotalUnits,
Sum(Q_02SharesByParcel.UnitsEffective)
AS SumOfUnitsEffective,
[SumOfUnitsEffective]/[TotalUnits] AS [Percent]
FROM Q_02SharesByParcel
INNER JOIN Q_01SharesByInvestment
ON Q_02SharesByParcel.Name
= Q_01SharesByInvestment.Name
GROUP BY Q_02SharesByParcel.Name,
Q_02SharesByParcel.Owner,
Q_01SharesByInvestment.TotalUnits
HAVING (((Q_02SharesByParcel.Owner)<>"--"));

[Q_03SharesByOwner] Query Datasheet View:

Name Owner TotalUnits SumOfUnitsEffective Percent
---- ----- ---------- ------------------- -------
Investment1 Owner1 400 250 62.50%
Investment1 Owner2 400 150 37.50%
Investment2 Owner2 1100 1100 100.00%
Investment3 Owner3 20 10 50.00%
Investment4 Owner4 1 1 100.00%


We add a sequence number to be used later in assigning values to columns
in the Datasheet. Owners later in the alphabet get pushed to the right.

[Q_04OwnerRecords] SQL:

SELECT Q_03SharesByOwner.Name,
Q_03SharesByOwner.Owner,
Q_03SharesByOwner.Percent,
Count(Q_03SharesByOwner_1.Owner) AS Seq
FROM Q_03SharesByOwner
INNER JOIN Q_03SharesByOwner AS Q_03SharesByOwner_1
ON Q_03SharesByOwner.Name = Q_03SharesByOwner_1.Name
WHERE (((Q_03SharesByOwner_1.Owner)
<=[Q_03SharesByOwner]![Owner]))
GROUP BY Q_03SharesByOwner.Name,
Q_03SharesByOwner.Owner, Q_03SharesByOwner.Percent;

[Q_04OwnerRecords] Query Datasheet View:

Name Owner Percent Seq
---- ----- ------- ---
Investment1 Owner1 62.50% 1
Investment1 Owner2 37.50% 2
Investment2 Owner2 100.00% 1
Investment3 Owner3 50.00% 1
Investment4 Owner4 100.00% 1


We specify a header for each column of owner names. Sequence number at
the beginning of each label keeps the columns sorted into the proper order.

[Q_05Owners] SQL:

SELECT Q_04OwnerRecords.Name,
Format$([Q_04OwnerRecords]![Seq],"00") & "OWNER"
AS Header,
Q_04OwnerRecords.Owner
FROM Q_04OwnerRecords
ORDER BY Q_04OwnerRecords.Name,
Format$([Q_04OwnerRecords]![Seq],"00") & "OWNER";

[Q_05Owners] Query Datasheet View:

Name Header Owner
---- ------ -----
Investment1 01OWNER Owner1
Investment1 02OWNER Owner2
Investment2 01OWNER Owner2
Investment3 01OWNER Owner3
Investment4 01OWNER Owner4


Same for each column of percentages.

[Q_06Percents] SQL:

SELECT Q_04OwnerRecords.Name,
Format$([Q_04OwnerRecords]![Seq],"00") & " PCT"
AS Header,
Format$([Q_04OwnerRecords]![Percent]*100,"#.0") & "%"
AS Pct
FROM Q_04OwnerRecords;

[Q_06Percents] Query Datasheet View:

Name Header Pct
---- ------ ---
Investment1 01 PCT 62.5%
Investment1 02 PCT 37.5%
Investment2 01 PCT 100.0%
Investment3 01 PCT 50.0%
Investment4 01 PCT 100.0%


Now we combine owners and percentages into one list.

[Q_07OwnersAndPercents] SQL:

SELECT Q_04OwnerRecords.Name,
Format$([Q_04OwnerRecords]![Seq],"00") & "OWNER"
AS Header,
Q_04OwnerRecords.Owner
FROM Q_04OwnerRecords
UNION ALL
SELECT Q_04OwnerRecords.Name,
Format$([Q_04OwnerRecords]![Seq],"00") & " PCT"
AS Header,
Format$([Q_04OwnerRecords]![Percent]*100,"#.0") & "%"
AS Pct
FROM Q_04OwnerRecords
ORDER BY Q_04OwnerRecords.Name, Header;

[Q_07OwnersAndPercents] Query Datasheet View:

Name Header Owner
---- ------ -----
Investment1 01 PCT 62.5%
Investment1 01OWNER Owner1
Investment1 02 PCT 37.5%
Investment1 02OWNER Owner2
Investment2 01 PCT 100.0%
Investment2 01OWNER Owner2
Investment3 01 PCT 50.0%
Investment3 01OWNER Owner3
Investment4 01 PCT 100.0%
Investment4 01OWNER Owner4


We combine the list with the [TotalUnits] from a previous Query.

[Q_08OwnersByInvestment] SQL:

SELECT Q_01SharesByInvestment.Name,
Q_01SharesByInvestment.TotalUnits,
Q_07OwnersAndPercents.Header,
Q_07OwnersAndPercents.Owner AS [Value]
FROM Q_07OwnersAndPercents
INNER JOIN Q_01SharesByInvestment
ON Q_07OwnersAndPercents.Name
= Q_01SharesByInvestment.Name
ORDER BY Q_01SharesByInvestment.Name,
Q_07OwnersAndPercents.Header;

[Q_08OwnersByInvestment] Query Datasheet View:

Name TotalUnits Header Value
---- ---------- ------ -----
Investment1 400 01 PCT 62.5%
Investment1 400 01OWNER Owner1
Investment1 400 02 PCT 37.5%
Investment1 400 02OWNER Owner2
Investment2 1100 01 PCT 100.0%
Investment2 1100 01OWNER Owner2
Investment3 20 01 PCT 50.0%
Investment3 20 01OWNER Owner3
Investment4 1 01 PCT 100.0%
Investment4 1 01OWNER Owner4


.... and we now have everything we need to list the results in the
desired format.

[Q_09Investments_Xtab] SQL:

TRANSFORM First(Q_08OwnersByInvestment.Value)
AS FirstOfValue
SELECT Q_08OwnersByInvestment.Name,
Q_08OwnersByInvestment.TotalUnits
FROM Q_08OwnersByInvestment
GROUP BY Q_08OwnersByInvestment.Name,
Q_08OwnersByInvestment.TotalUnits
PIVOT Q_08OwnersByInvestment.Header;

[Q_09Investments_Xtab] Query Datasheet View:

Name TotalUnits 01 PCT 01OWNER 02 PCT 02OWNER
---- ---------- ------ ------- ------ -------
Investment1 400 62.5% Owner1 37.5% Owner2
Investment2 1100 100.0% Owner2
Investment3 20 50.0% Owner3
Investment4 1 100.0% Owner4


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.