Picking data out of access into excel

  • Thread starter Thread starter Kashgarinn
  • Start date Start date
K

Kashgarinn

Hi there and thanks for reading this.

I have an access database called 'transactions' and in it are thes
columns which I'm interested in:

Date
type
quantity
profit

Each type of object which has been sold has a quantity and profi
tagged on to it. I want to multiply profit and quantity together an
then display the total in a cell in excel.

I'd also like to be able to display the different types, the tota
quantity sold for those types and total profit for that type.

I've tried using pivotcharts, but for some reason I get strange result
as the pivottable option stacks the columns together in some strange wa
(all quantity 1's, 2's and 3's get stacked together and the sum of th
profits displayed.. which is not what I want), and I can't fathom ho
to change it to what I want.

K
 
Shameless bump :)

No one know how to do this?

Should I perhaps repost this question in the programming section?

K
 
K

Pivot tables would be my suggestion for what you want. I think we may just
need a little more detail of possible the layout you are getting

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I think you should prepare several total queries *in Access* (to calculate
the totals you need), and then get the data into Excel from the Access
*Queries* (not from the transactions table). Total queries are very easy in
Access, and Microsoft Query will see the Access queries as if they was
actual tables. Then you can use Excel for formatting/presentation purposes.


Tonín
 
Well, I could of course have 2 pivot tables, one with the unique I
column and 'quantity' and then the other with unique ID and 'profit'
and then just let excel multiply the 'quantity' with profit for eac
row, and then just add that column together.. but it doesn't seem to b
a clean solution.. although simpler than having alot of queries.

Is there a way to make the pivottable do it for you somehow? Make i
sort by unique ID in the left column, and pick 'profit' and 'quantity
from the transaction database and automatically multiply the
together?

I'll have to play around with this.. I don't like having to have
pivottables to do this for some reason.

Thanks for your input :) If you have more, I'd appreciate it. BT
total pivottable noob, so I'm using the autopivottable thingy an
perhaps that is trying to foresee what I want to do (and miserabl
failing).

K
 
:-)

Unless I misunderstood your problem, I want suggest again: at least, you
should use this query [one query] as a data source:

SELECT date, type, quantity, profit, [quantity]*[profit] AS quantityprofit
FROM transactions;

You will get five fields, the last one, quantity and profit already
multiplied. Then, use the subtotals feature (in data menu) or a pivot table
as you prefer.
I usually choose queries for totals, as I told you, but ... probably because
I'm quite more comfortable with that, not because any strong reason.


Tonín
 
Tonín said:
Unless I misunderstood your problem, I want suggest again: at least, you
should use this query [one query] as a data source:

SELECT date, type, quantity, profit, [quantity]*[profit] AS quantityprofit
FROM transactions;
Click to expand...

Or possibly:

SELECT SUM(quantity)*SUM(profit) AS total_quantity_profit
FROM transactions;

Jamie.

--
 
Hi, Jaimie

Yes, I agree. That query you posted is intended to provide the grand total,
which is the first thing the user wanted. In fact, I suggested that in a
former post to this question as a possible approach.

(BTW, I guess user meant that: SELECT SUM(quantity*profit) AS
total_quantity_profit FROM transactions; ':-? )

Reason for my last query is the user posted as a 2nd need (as far I can
understand English - I'm Spanish, my English is so little, so sorry) this
requirement:

"I'd also like to be able to display the different types, the total
quantity sold for those types and total profit for that type."

He expressed having a problem with multiplying quality*profit inside an
Excel pivot table (and being reluctant to use several queries). Then I
thought at least a *single* query could help him to get that 2nd requirement
as the query allows him calculate the grand total in Excel using a single
pivot table, or subtotals, or a formula, or whatever.



Tonín

Jamie Collins said:
Tonín said:
Unless I misunderstood your problem, I want suggest again: at least, you
should use this query [one query] as a data source:

SELECT date, type, quantity, profit, [quantity]*[profit] AS quantityprofit
FROM transactions;
Click to expand...

Or possibly:

SELECT SUM(quantity)*SUM(profit) AS total_quantity_profit
FROM transactions;

Jamie.

--
Click to expand...
 
Back
Top