Query help - anyone?

[shtuks]

Hi,

I have four columns in my table. First is ID (Number), second DATE
(DATE/TIME), third TIME (Text) and the fourth AMOUNT (Number).

My table looks something like this:

123 || 5.7.2006 || 07:15 || 35
232 || 8.7.2006 || 07:39 || 45
234 || 8.7.2006 || 07:43 || 21
432 || 9.7.2006 || 07:55 || 23
....


I'am trying to make two queries but I still didn't find the way to do
it propertly.

First I want to make sum of all amounts for period from 5.7.2006. to
9.7.2006. (include those two dates). I had some problems with date
format and I stopped there so I need your help to do that.

Other query is little more complicated. For every day in month I must
make sum of amounts for every specific time period (2 hours).

For date 8.7.2006., sum for period from 7:00 to 9:00 is 66 and so on...


I hope that I was clear enoguh and that there will be someone to help
me.

Thx

 

[Guest]

First off are your dates DD.MM.YY or MM.DD.YY format?

Second it's really too bad that you don't have your Dates and Times in the
same field. Then in a Report you could easily do what you want with the
second problem using the Group interval property.

 

[Douglas J. Steele]

You can create a query that simply adds the two together so that it is a
true Date/Time, so it doesn't really matter that they're in separate fields
(although I agree they should be in the same field)

SELECT ID, [Date] + [Time] AS TimestampValue, Amount FROM MyTable

Another potential source of problems could be that Date and Time are
reserved words, and shouldn't be used as field names. If they can't be
changed, at least make sure you put square brackets around them, as I did
above.

I'm guessing that those dates are dd.mm.yyyy. If so, it's important to
realize that when specifying constant dates, they must be specified in
mm/dd/yyyy format. It would probably be a good idea to read Allen Browne's
"International Dates in Access" at http://allenbrowne.com/ser-36.html or
what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[shtuks]

Thanks!

I managed somehow to work out the problem... but now I have the other
one :/

I want to manage query from combo box...

To be more clear:

When user makes his choice (from combo box) with date and time I want
from query to make the SUM of amount and shows the result.

I made the basic query but I don't know how to apply users choice in
query if user choses sum for period of 07/07/2006 and time 12:00 to
17:00 (for example) or if he choses 08/09/2006 and time 11:00 to 14,
etc...

Could someone give me some tip...thx

Douglas J. Steele je napisao/la:

You can create a query that simply adds the two together so that it is a
true Date/Time, so it doesn't really matter that they're in separate fields
(although I agree they should be in the same field)

SELECT ID, [Date] + [Time] AS TimestampValue, Amount FROM MyTable

Another potential source of problems could be that Date and Time are
reserved words, and shouldn't be used as field names. If they can't be
changed, at least make sure you put square brackets around them, as I did
above.

I'm guessing that those dates are dd.mm.yyyy. If so, it's important to
realize that when specifying constant dates, they must be specified in
mm/dd/yyyy format. It would probably be a good idea to read Allen Browne's
"International Dates in Access" at http://allenbrowne.com/ser-36.html or
what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First off are your dates DD.MM.YY or MM.DD.YY format?

Second it's really too bad that you don't have your Dates and Times in the
same field. Then in a Report you could easily do what you want with the
second problem using the Group interval property.

 

[Douglas J. Steele]

What exactly is the combo box returning?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks!

I managed somehow to work out the problem... but now I have the other
one :/

I want to manage query from combo box...

To be more clear:

When user makes his choice (from combo box) with date and time I want
from query to make the SUM of amount and shows the result.

I made the basic query but I don't know how to apply users choice in
query if user choses sum for period of 07/07/2006 and time 12:00 to
17:00 (for example) or if he choses 08/09/2006 and time 11:00 to 14,
etc...

Could someone give me some tip...thx

Douglas J. Steele je napisao/la:

You can create a query that simply adds the two together so that it is a
true Date/Time, so it doesn't really matter that they're in separate
fields
(although I agree they should be in the same field)

SELECT ID, [Date] + [Time] AS TimestampValue, Amount FROM MyTable

Another potential source of problems could be that Date and Time are
reserved words, and shouldn't be used as field names. If they can't be
changed, at least make sure you put square brackets around them, as I did
above.

I'm guessing that those dates are dd.mm.yyyy. If so, it's important to
realize that when specifying constant dates, they must be specified in
mm/dd/yyyy format. It would probably be a good idea to read Allen
Browne's
"International Dates in Access" at http://allenbrowne.com/ser-36.html or
what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First off are your dates DD.MM.YY or MM.DD.YY format?

Second it's really too bad that you don't have your Dates and Times in
the
same field. Then in a Report you could easily do what you want with the
second problem using the Group interval property.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,

I have four columns in my table. First is ID (Number), second DATE
(DATE/TIME), third TIME (Text) and the fourth AMOUNT (Number).

My table looks something like this:

123 || 5.7.2006 || 07:15 || 35
232 || 8.7.2006 || 07:39 || 45
234 || 8.7.2006 || 07:43 || 21
432 || 9.7.2006 || 07:55 || 23
....


I'am trying to make two queries but I still didn't find the way to do
it propertly.

First I want to make sum of all amounts for period from 5.7.2006. to
9.7.2006. (include those two dates). I had some problems with date
format and I stopped there so I need your help to do that.

Other query is little more complicated. For every day in month I must
make sum of amounts for every specific time period (2 hours).

For date 8.7.2006., sum for period from 7:00 to 9:00 is 66 and so
on...


I hope that I was clear enoguh and that there will be someone to help
me.

Thx