Query Criteria

[Rob]

How can I setup a query to pull year to date budget info? I have a table (tbl
budget) that contains budget information by project. I have "month" as a
field name so each project is enter 12 times in the table (1 for each month).
I will need to obtain year to date budget info throughout the year next year.
I am not sure how to specify the query to pull a certain time line as the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month = [tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name];

 

[ShaneDevenshire]

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and time.

 

[ShaneDevenshire]

Hi,

Here's one example
WHERE (((Table1.Date)>=#9/1/2007# And (Table1.Date)<=#11/30/2007#))
--
Thanks,
Shane Devenshire

How would this work if my fiscal year is October through September?

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and time.
--
Cheers,
Shane Devenshire

How can I setup a query to pull year to date budget info? I have a table (tbl
budget) that contains budget information by project. I have "month" as a
field name so each project is enter 12 times in the table (1 for each month).
I will need to obtain year to date budget info throughout the year next year.
I am not sure how to specify the query to pull a certain time line as the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month = [tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name];

 

[Pieter Wijnen]

that's a short year <g>

Pieter

Hi,

Here's one example
WHERE (((Table1.Date)>=#9/1/2007# And (Table1.Date)<=#11/30/2007#))
--
Thanks,
Shane Devenshire

How would this work if my fiscal year is October through September?

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and
time.
--
Cheers,
Shane Devenshire


:

How can I setup a query to pull year to date budget info? I have a
table (tbl
budget) that contains budget information by project. I have "month"
as a
field name so each project is enter 12 times in the table (1 for each
month).
I will need to obtain year to date budget info throughout the year
next year.
I am not sure how to specify the query to pull a certain time line as
the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID
Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month =
[tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID
Pook],
[tbl budget].[Project Name];

 

[ShaneDevenshire]

Hi Pieter,

Just an example.

Bob

Generally reserve words don't allow there use anywhere other than as they
are defined, however, I tested the above and it did work. Now I have to
agree that using these word is a bad idea but it does seem to work in my
tests.

--
Cheers,
Shane Devenshire

Hi -

Both Month and Date are reserved words and shouldn't be used as field names.
To avoid problems in the future, you might ought to rename these fields now.

Bob

that's a short year <g>

Pieter

Hi,

[quoted text clipped - 36 lines]

Pook],
[tbl budget].[Project Name];

 

[Pieter Wijnen]

It works, I never implied anything else, but you must be damn sure to always
prefix or [] when you use them

Pieter

Hi Pieter,

Just an example.

Bob

Generally reserve words don't allow there use anywhere other than as they
are defined, however, I tested the above and it did work. Now I have to
agree that using these word is a bad idea but it does seem to work in my
tests.

--
Cheers,
Shane Devenshire

Hi -

Both Month and Date are reserved words and shouldn't be used as field
names.
To avoid problems in the future, you might ought to rename these fields
now.

Bob

that's a short year <g>

Pieter

Hi,

[quoted text clipped - 36 lines]
Pook],
[tbl budget].[Project Name];