Query problem: dividing

[sc2ptical]

Hi there,

I want to make a query and from this query a report. Give you an example.

Say that you have 5 sellers and 5 hardwares that the sellers need to sell.
You keep it update in the DB which seller sels what and want to make a
report what the sellers have selled so far, something like this,

Total HW1 HW2 HW3 HW4 HW5
Seller 1 100 25 30 5 10 30
Seller 2 255 75 75 50 0 55
Seller 3 190 100 50 30 10 0
Seller 4 200 50 60 40 30 20
Seller 5 0 0 0 0 0 0

I was wondering if you need to make this in a qeury? Also those fields are
in one table. Who can please help or show me a link or whatever. TIA.

Sc2ptical

 

[Michel Walsh]

Hi,


A crosstab query. I assume you use Jet.


TRANSFORM Nz( SUM(amount), 0) As x
SELECT Seller, SUM(x) As Total
FROM myTable
GROUP BY Seller
PIVOT Hardware




Hoping it may help,
Vanderghast, Access MVP

 

[Idontknow]

Hi there,

Thanks for the help. I'm going to try this and see if this one helps.
I just started to use MS Access and I have no any experience with this
software. Simpel DB is no problem but more complex reports is, so I'm
already figuring out how I can make them. Jet, I have no idea if I use it
or not, don't see much to me. SOrry that I sound like a newbie because I'm
but always willing to learn and able to fix things, also this. I let you
know if it works.

 

[Michel Walsh]

Hi,


Note that even if I supply the SQL statement, for starting, it may
be preferable to use the graphical query designer, or the crosstab query
wizard, in that case.



Hoping it may help,
Vanderghast, Access MVP