Simple Query does not work

[gruiz1998]

Hi Everyone, I'm working with VB6 and Access as a Database (local application) I no not get a query to work:


From the following query
StrConsulta = "SELECT * FROM MedReg WHERE IdService =" & IdService & StrDate & " ORDER BY IdMed"
to a Database in Access I get (In order to save space I changed column names):

A IdMed C D E F G

2, 405, 8ABR2009AC, 20, 10, SI, 4/8/2009
2, 405, 8ABR2009AB, 10, 8, SI, 4/8/2009
2, 407, 8ABR2009AB, 10, 8, SI, 4/8/2009
2, 407, 8ABR2009AC, 20, 15, SI, 4/8/2009
2, 622, 7ABR2009AA, 9, 5, SI, 4/7/2009
2, 622, 8ABR2009AA, 10, 8, SI, 4/8/2009
2, 622, 8ABR2009AC, 20, 10, SI, 4/8/2009
2, 643, 8ABR2009AC, 20, 15, SI, 4/8/2009
2, 643, 7ABR2009AC, 5, 4, SI, 4/8/2009
2, 643, 8ABR2009AC, 10, 8, SI, 4/8/2009
2, 644, 8ABR2009AC, 20, 10, SI, 4/8/2009
2, 644, 8ABR2009AC, 10, 8, SI, 4/8/2009
2, 644, 7ABR2009AA, 3, 3, SI, 4/8/2009
2, 685, 8ABR2009AA, 20, 15, SI, 4/8/2009
2, 685, 7ABR2009AA, 0, 0, SI, 4/8/2009
2, 685, 8ABR2009AA, 20, 15, SI, 4/8/2009
2, 685, 8ABR2009AA, 10, 8, SI, 4/8/2009

However What I need is something like:

Running the following query should work well to get a "desired result":
SELECT
DISTINCT IdMed, SUM( MedReg.CantidadSolicitada), SUM( MedReg.CantidadRecibida) FROM
MedReg
WHERE
MedReg.IdService =2 AND
MedReg.IdMed = 405 AND
MedReg.IdMed = 407 AND
MedReg.IdMed = 622 AND
MedReg.IdMed = 643 AND
MedReg.IdMed = 644 AND
MedReg.IdMed = 685 AND
MedReg.DateBegin >= #04/01/2009# AND
MedReg.DateEnd <= #04/16/2009#
GROUP BY
IdMed

"desired result"
A IdMed C D E F G
2, 405, 8ABR2009AC, 30, 18, SI, 4/8/2009
2, 407, 8ABR2009AB, 30, 23, SI, 4/8/2009
2, 622, 7ABR2009AA, 39, 23, SI, 4/7/2009
2, 643, 8ABR2009AC, 35, 27, SI, 4/8/2009
2, 644, 8ABR2009AC, 33, 22, SI, 4/8/2009
2, 685, 8ABR2009AA, 50, 15, SI, 4/8/2009




or
Running the following query should work well to get a "desired result":
SELECT
DISTINCT IdMed, SUM( MedReg.CantidadSolicitada), SUM( MedReg.CantidadRecibida) FROM
MedReg
WHERE
MedReg.IdService =2 AND
MedReg.IdMed = 622 AND
MedReg.IdMed = 643 AND
MedReg.IdMed = 644 AND
MedReg.IdMed = 685 AND
MedReg.DateBegin >= #04/01/2009# AND
MedReg.DateEnd <= #04/16/2009#
GROUP BY
IdMed

"desired result"
A IdMed C D E F G
2, 622, 7ABR2009AA, 39, 23, SI, 4/7/2009
2, 643, 8ABR2009AC, 35, 27, SI, 4/8/2009
2, 644, 8ABR2009AC, 33, 22, SI, 4/8/2009
2, 685, 8ABR2009AA, 50, 15, SI, 4/8/2009



or
Running the following query should work well to get a "desired result":
SELECT
DISTINCT IdMed, SUM( MedReg.CantidadSolicitada), SUM( MedReg.CantidadRecibida) FROM
MedReg
WHERE
MedReg.IdService =2 AND
MedReg.IdMed = 405 AND
MedReg.IdMed = 407 AND
MedReg.DateBegin >= #04/01/2009# AND
MedReg.DateEnd <= #04/16/2009#
GROUP BY
IdMed

"desired result"
A IdMed C D E F G
2, 405, 8ABR2009AC, 30, 18, SI, 4/8/2009
2, 407, 8ABR2009AB, 30, 23, SI, 4/8/2009


IdMed is what I'll be changing in the query according to my programming:


However it doesn't work in Access, The query does not send any data, not even an error, any idea?

 

[BrianLe]

Could it be that you don't have a field DateEnd?

 

[dhookom]

This statement

 MedReg.IdMed = 405 AND 
    MedReg.IdMed = 407 AND

should never return any records since IdMed can't equal 405 and 407.
I think you need:

WHERE 
    MedReg.IdService  =2 AND 
    MedReg.IdMed IN ( 622, 643, 644, 685) AND 
    MedReg.DateBegin >= #04/01/2009# AND 
    MedReg.DateEnd <= #04/16/2009#

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Furthermore, why using the DISTINCT predicate in an aggregate query ???

 

[gruiz1998]

Thanks to both of you, however at the top of the question
there is an structure like

A IdMed C D E F G

2, 405, 8ABR2009AC, 20, 10, SI, 4/8/2009
2, 405, 8ABR2009AB, 10, 8, SI, 4/8/2009
2, 407, 8ABR2009AB, 10, 8, SI, 4/8/2009
2, 407, 8ABR2009AC, 20, 15, SI, 4/8/2009

And the desired result should be:

A IdMed D E
2 405 30 18
2 407 30 23

Running the following query does not work
SELECT
DISTINCT IdMed, SUM( MedReg.CantidadSolicitada),
SUM( MedReg.CantidadRecibida)
FROM
MedReg
WHERE
MedReg.IdService =2 AND
MedReg.IdMed = 405 AND
MedReg.IdMed = 407 AND
MedReg.DateBegin >= #04/01/2009# AND
MedReg.DateEnd <= #04/16/2009#
GROUP BY
IdMed

Neither work

SELECT
DISTINCT IdMed, SUM( MedReg.CantidadSolicitada),
SUM( MedReg.CantidadRecibida)
FROM
MedReg
WHERE
MedReg.IdServicio =2 AND
MedReg.IdMedicamento IN (405,407)
MedReg.DateBegin >= #04/01/2009# AND
RegistroDiarioMedicamentos.DateEnd <= #04/20/2009#
GROUP BY
IdMed

Any other Idea?

 

[lespaul]

if this is your structure:
[tt]
A IdMed C D E F G

2, 405, 8ABR2009AC, 20, 10, SI, 4/8/2009
2, 405, 8ABR2009AB, 10, 8, SI, 4/8/2009
2, 407, 8ABR2009AB, 10, 8, SI, 4/8/2009
2, 407, 8ABR2009AC, 20, 15, SI, 4/8/2009
[/tt]
and you want these results [tt]
A IdMed D E
2 405 30 18
2 407 30 23 [/tt]

then this query will work:

SELECT A, IdMed, SUM(D), SUM(E) FROM TableName
GROUP BY A, IdMed

if however you really want additional fields in your final results along with this information then you'll need a different query.

HTH

Leslie

Come join me at New Mexico Linux Fest!

 

[dhookom]

Use the 2nd SQL since the 1st will always fail for reasons I mention earlier. You can also get rid of DISTINCT as PH suggested.

I only see one date field in your sample records.

Start by removing all criteria and begin adding back criteria until you get unexpected results.

Also, I would check your date formats since you might be located in another country where dates are displayed as d/m/y.

Duane
Hook'D on Access
MS Access MVP

 

[gruiz1998]

Thank you, I tried and finally worked with a UNION statement.