Query for Columns?

I

ismail

Can someone help in this issue...please

i have a table called sales with column...
Petrol | Diesel | Motel | Store |

500 | 600 | 44 | 200 |
77 | 77 | 600 | 800 |
100 | 400 | 600 | 800 |

etc....as valuse...i will select through combo... petrol or disesel or
motel.......
according to my selection..the query should give all the valuse (ex. values
of petrol)
from the table...

may be my question is very very silly...but please help me...i want have
only one query...


Thanks in Advance
 
M

MGFoster

ismail said:
Can someone help in this issue...please

i have a table called sales with column...
Petrol | Diesel | Motel | Store |

500 | 600 | 44 | 200 |
77 | 77 | 600 | 800 |
100 | 400 | 600 | 800 |

etc....as valuse...i will select through combo... petrol or disesel or
motel.......
according to my selection..the query should give all the valuse (ex. values
of petrol)
from the table...

may be my question is very very silly...but please help me...i want have
only one query...
Click to expand...

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

You table is incorrectly designed. It should be like this:

Sales_Date Product Sales_Amount
1/1/2005 Petrol 500
1/1/2005 Desel 600
1/1/2005 Motel 44
1/1/2005 Store 200

Then the query is very easy:

SELECT Product, Sum(Sales_Amount) As Total_Sales
FROM Sales
WHERE Product = 'Petrol'
AND Sales_Date BETWEEN #1/1/2005# And #1/31/2005#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw9wh4echKqOuFEgEQJv/ACgx5tGfZjHRDEv1lB3FJhihi1eKtYAn3iu
DUa0DcXMMhNw2/OeAbelojP6
=+0PV
-----END PGP SIGNATURE-----
 
I

ismail

Dear MGFoster..thanks for ur reply...u see...i almost completed my
software..with this table design....i do not want to change the
design..b'cause..i have to modify
many things.....

is there any trick i can do....now...or i have to use query for each column...
please reply
 
M

MGFoster

ismail said:
Dear MGFoster..thanks for ur reply...u see...i almost completed my
software..with this table design....i do not want to change the
design..b'cause..i have to modify
many things.....

is there any trick i can do....now...or i have to use query for each column...
please reply
Click to expand...

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

Your table is not in Normal form. In the long run you'll be sorry, 'cuz
you'll be performing many weird maintenance & work-arounds projects to
overcome the bad design. It's best to correct the bad design before you
put the db in production.

The following is a minor work-around:

You have to create an SQL string using VBA. Something like this (Access
2000):

Const SQL = "SELECT |1 FROM MyTable WHERE |1 = "

Dim strSQL As String

strSQL = Replace(SQL,"|1", Me!cboColumnName)
strSQL = strSQL & "'" & Me!cboSearchValue & "'"

Then use the strSQL string as you desire (put in a query, open a
recordset, etc.).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw9+w4echKqOuFEgEQJD9QCg5LAiyKtfQkQ1HSVHijJ0FrnNF2oAoPjk
8C5d74x0dlr7ZkK1U9aW/C+v
=kJiC
-----END PGP SIGNATURE-----
 
I

ismail

thanks ...i could manage.....could u please give me some sample code
"How to connect this VBA query..to Report"

because i know only "by wizards" making connecting reports and querys...

Regards
 
I

ismail

i get this error message..."constant expression required "
what is the error in my query please tell

Const SQL = "SELECT petrol,stid FROM details where stid='" & Me!stidcmb & "'"

Dim strSQL As String

strSQL = Replace(SQL, "petrol", Me!activitycmb)

Thanks in Advance
 
J

John Spencer (MVP)

You can't define the "contents" of a Constant using a variable - if you could
then you wouldn't have a constant.

So,

Const SQL = "SELECT petrol, stid FROM details where stid='ReplaceThis'"
Dim strSQL as String
strSQL = Replace(SQL, "ReplaceThis", Me!activitycmb)

or

Dim strSQL as String
StrSQL = "SELECT petrol, stid FROM details where stid='ReplaceThis'"
strSQL = Replace(strSQL, "ReplaceThis", Me!activitycmb)
 
Top