Fiscal Month Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that uses a query to get the data I need. Basically it includes:
Part No
Price
Qty
Need to Order Date

Now I need to run a query or design a report so that I can have a listing of
all parts that need to be ordered within each fiscal month. Of course our
fiscal months are not like a regular month (that would be too easy :-))). I
have a table with the fiscal months for today out to the end of 2010. It
includes:
Fiscal Month
Fiscal Year
Start Date
End Date
Wasn't sure if I needed to combine Fiscal Month and Fiscal Year.

Any help on how I can get this into a report format or a query to save as a
report would be greatly appreciated!
 
Try this --
SELECT nanette.[Part No], nanette.Price, nanette.Qty, nanette.[Need to Order
Date], FiscalMonth.FiscalYear, FiscalMonth.FiscalMonth,
FiscalMonth.StartDate, FiscalMonth.EndDate
FROM FiscalMonth, nanette
WHERE (((nanette.[Need to Order Date]) Between [StartDate] And [EndDate]));

Substitue you table names.
 
Hi Karl,

It's not working. When I try to run the query it asks for the paramenters.
Here's my query. There are no common fields between the tables, so I can't
connect them.

SELECT qry60DayForecastTest3.[Part No] AS Expr1PN,
qry60DayForecastTest3.Price AS Expr2, qry60DayForecastTest3.Qty AS Expr3,
qry60DayForecastTest3.[Need to Order
Date] AS Expr4, tblFiscalMonths.FiscalYear AS Expr5,
tblFiscalMonths.FiscalMonth AS Expr6, tblFiscalMonths.StartDate AS Expr7,
tblFiscalMonths.EndDate AS Expr8
FROM qry60DayForecastTest3, tblFiscalMonths
WHERE ((([qry60DayForecastTest3].[Need to Order Date]) Between [StartDate]
And [EndDate]));

Do you know what I'm doing wrong and how to fix it?


KARL DEWEY said:
Try this --
SELECT nanette.[Part No], nanette.Price, nanette.Qty, nanette.[Need to Order
Date], FiscalMonth.FiscalYear, FiscalMonth.FiscalMonth,
FiscalMonth.StartDate, FiscalMonth.EndDate
FROM FiscalMonth, nanette
WHERE (((nanette.[Need to Order Date]) Between [StartDate] And [EndDate]));

Substitue you table names.

Nanette said:
I have a query that uses a query to get the data I need. Basically it includes:
Part No
Price
Qty
Need to Order Date

Now I need to run a query or design a report so that I can have a listing of
all parts that need to be ordered within each fiscal month. Of course our
fiscal months are not like a regular month (that would be too easy :-))). I
have a table with the fiscal months for today out to the end of 2010. It
includes:
Fiscal Month
Fiscal Year
Start Date
End Date
Wasn't sure if I needed to combine Fiscal Month and Fiscal Year.

Any help on how I can get this into a report format or a query to save as a
report would be greatly appreciated!
Click to expand...
Click to expand...
 
it asks for the paramenters
You did not say what parameters it ask for.

SELECT qry60DayForecastTest3.[Part No],
qry60DayForecastTest3.Price, qry60DayForecastTest3.Qty,
qry60DayForecastTest3.[Need to Order Date], tblFiscalMonths.FiscalYear,
tblFiscalMonths.FiscalMonth, tblFiscalMonths.StartDate,
tblFiscalMonths.EndDate
FROM qry60DayForecastTest3, tblFiscalMonths
WHERE ((([qry60DayForecastTest3].[Need to Order Date]) Between [StartDate]
And [EndDate]));

Why did you query haveall alias and not just the field names?

Nanette said:
Hi Karl,

It's not working. When I try to run the query it asks for the paramenters.
Here's my query. There are no common fields between the tables, so I can't
connect them.

SELECT qry60DayForecastTest3.[Part No] AS Expr1PN,
qry60DayForecastTest3.Price AS Expr2, qry60DayForecastTest3.Qty AS Expr3,
qry60DayForecastTest3.[Need to Order
Date] AS Expr4, tblFiscalMonths.FiscalYear AS Expr5,
tblFiscalMonths.FiscalMonth AS Expr6, tblFiscalMonths.StartDate AS Expr7,
tblFiscalMonths.EndDate AS Expr8
FROM qry60DayForecastTest3, tblFiscalMonths
WHERE ((([qry60DayForecastTest3].[Need to Order Date]) Between [StartDate]
And [EndDate]));

Do you know what I'm doing wrong and how to fix it?


KARL DEWEY said:
Try this --
SELECT nanette.[Part No], nanette.Price, nanette.Qty, nanette.[Need to Order
Date], FiscalMonth.FiscalYear, FiscalMonth.FiscalMonth,
FiscalMonth.StartDate, FiscalMonth.EndDate
FROM FiscalMonth, nanette
WHERE (((nanette.[Need to Order Date]) Between [StartDate] And [EndDate]));

Substitue you table names.

Nanette said:
I have a query that uses a query to get the data I need. Basically it includes:
Part No
Price
Qty
Need to Order Date

Now I need to run a query or design a report so that I can have a listing of
all parts that need to be ordered within each fiscal month. Of course our
fiscal months are not like a regular month (that would be too easy :-))). I
have a table with the fiscal months for today out to the end of 2010. It
includes:
Fiscal Month
Fiscal Year
Start Date
End Date
Wasn't sure if I needed to combine Fiscal Month and Fiscal Year.

Any help on how I can get this into a report format or a query to save as a
report would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Your posted field names were Start Date and End Date (note the spaces). The
query contains StartDate and EndDate (note the lack of spaces). Also since
the field names apparently have spaces you need to surround then names with
[]. Also note the same space problem with Fiscal Month and Fiscal Year

I would try the following (which cannot be done in the design (grid) view.
SELECT qry60DayForecastTest3.[Part No] AS Expr1PN
, qry60DayForecastTest3.Price
, qry60DayForecastTest3.Qty
, qry60DayForecastTest3.[Need to Order Date]
, tblFiscalMonths.[Fiscal Year]
, tblFiscalMonths.[Fiscal Month]
, tblFiscalMonths.[Start Date]
, tblFiscalMonths.[End Date]
FROM qry60DayForecastTest3 INNER JOIN tblFiscalMonths
ON qry60DayForecastTest3].[Need to Order Date] >= tblFiscalMonths.[Start
Date]
And qry60DayForecastTest3].[Need to Order Date] <= tblFiscalMonths.[End
Date]
WHERE [Need to Order Date] > Date()

or you can used Karl's suggested solution and fix the field names. This one
can be viewed in the design (grid) view.
SELECT qry60DayForecastTest3.[Part No],
qry60DayForecastTest3.Price, qry60DayForecastTest3.Qty,
qry60DayForecastTest3.[Need to Order Date]
, tblFiscalMonths.[Fiscal Year],
tblFiscalMonths.[Fiscal Month]
, tblFiscalMonths.[Start Date],
tblFiscalMonths.[End Date]
FROM qry60DayForecastTest3, tblFiscalMonths
WHERE ((([qry60DayForecastTest3].[Need to Order Date]) Between [Start Date]
And [End Date]));

Nanette said:
Hi Karl,

It's not working. When I try to run the query it asks for the paramenters.
Here's my query. There are no common fields between the tables, so I can't
connect them.

SELECT qry60DayForecastTest3.[Part No] AS Expr1PN,
qry60DayForecastTest3.Price AS Expr2, qry60DayForecastTest3.Qty AS Expr3,
qry60DayForecastTest3.[Need to Order
Date] AS Expr4, tblFiscalMonths.FiscalYear AS Expr5,
tblFiscalMonths.FiscalMonth AS Expr6, tblFiscalMonths.StartDate AS Expr7,
tblFiscalMonths.EndDate AS Expr8
FROM qry60DayForecastTest3, tblFiscalMonths
WHERE ((([qry60DayForecastTest3].[Need to Order Date]) Between [StartDate]
And [EndDate]));

Do you know what I'm doing wrong and how to fix it?


KARL DEWEY said:
Try this --
SELECT nanette.[Part No], nanette.Price, nanette.Qty, nanette.[Need to
Order
Date], FiscalMonth.FiscalYear, FiscalMonth.FiscalMonth,
FiscalMonth.StartDate, FiscalMonth.EndDate
FROM FiscalMonth, nanette
WHERE (((nanette.[Need to Order Date]) Between [StartDate] And
[EndDate]));

Substitue you table names.

Nanette said:
I have a query that uses a query to get the data I need. Basically it
includes:
Part No
Price
Qty
Need to Order Date

Now I need to run a query or design a report so that I can have a
listing of
all parts that need to be ordered within each fiscal month. Of course
our
fiscal months are not like a regular month (that would be too easy
:-))). I
have a table with the fiscal months for today out to the end of 2010.
It
includes:
Fiscal Month
Fiscal Year
Start Date
End Date
Wasn't sure if I needed to combine Fiscal Month and Fiscal Year.

Any help on how I can get this into a report format or a query to save
as a
report would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top