Monthly Inventory Report

  • Thread starter Thread starter HubbyMax
  • Start date Start date
H

HubbyMax

I am setting up an inventory program that requires a monthy billing report
that shows each departments totals for products ordered and what their monthy
charge is. This report must be divided by each department. Currently it says
there is a problem in the "FROM" line but has also said there is problems in
the "INNER JOIN" areas. My Database properties for this form is as follows.
The report is accessed by a date peramiter box;

SELECT DISTINCTROW Products2.StockNu, Products2.ProductName, Dept2.Cust2,
Dept2.DeptName, [Products2].AvgUnitPrice AS [Avg Unit Price],
Sum([Req1].QuantityIssued) AS [Total Units] FROM Products2 INNER JOIN (Dept2
INNER JOIN [Req1]. ON ([ Dept2].Cust2=[Req1].Cust) ON
Products2.StockNu=[Req1].StockNu2) WHERE
(([Req1].ReqFilledDate)<=forms![Report Date Range]!BeginDate) And
([Req1].ReqFilledDate<=forms![Report Date Range]!EndDate) GROUP BY
Products2.StockNu, Products2.ProductName, Dept2.Cust2, Dept2.Name;

Unfortunatly this does not work. My tables are:

Products2, holds all product info
Req1, holds requisition info from departments
Dept2, Info for all departments
 
Off-hand, it looks like you have an extra space character here, after the
first opening square bracket ( [ )

ON ([ Dept2].Cust2=[Req1].Cust)


Also, this inequality does not look correct to me:

WHERE (([Req1].ReqFilledDate)<=forms![Report Date Range]!BeginDate)

For the beginning date, shouldn't that be >= (greater than or equal to)
instead of <= (less than or equal to)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Thanks. I will give it a try. You are correct. Just for info I am using
Access 2000. The string shoulod result in a product usage by department
withing the request dates grouped by Department. ONce agfain Thanks.

Tom Wickerath said:
Off-hand, it looks like you have an extra space character here, after the
first opening square bracket ( [ )

ON ([ Dept2].Cust2=[Req1].Cust)


Also, this inequality does not look correct to me:

WHERE (([Req1].ReqFilledDate)<=forms![Report Date Range]!BeginDate)

For the beginning date, shouldn't that be >= (greater than or equal to)
instead of <= (less than or equal to)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________


HubbyMax said:
I am setting up an inventory program that requires a monthy billing report
that shows each departments totals for products ordered and what their monthy
charge is. This report must be divided by each department. Currently it says
there is a problem in the "FROM" line but has also said there is problems in
the "INNER JOIN" areas. My Database properties for this form is as follows.
The report is accessed by a date peramiter box;

SELECT DISTINCTROW Products2.StockNu, Products2.ProductName, Dept2.Cust2,
Dept2.DeptName, [Products2].AvgUnitPrice AS [Avg Unit Price],
Sum([Req1].QuantityIssued) AS [Total Units] FROM Products2 INNER JOIN (Dept2
INNER JOIN [Req1]. ON ([ Dept2].Cust2=[Req1].Cust) ON
Products2.StockNu=[Req1].StockNu2) WHERE
(([Req1].ReqFilledDate)<=forms![Report Date Range]!BeginDate) And
([Req1].ReqFilledDate<=forms![Report Date Range]!EndDate) GROUP BY
Products2.StockNu, Products2.ProductName, Dept2.Cust2, Dept2.Name;

Unfortunatly this does not work. My tables are:

Products2, holds all product info
Req1, holds requisition info from departments
Dept2, Info for all departments
Click to expand...
Click to expand...
 
I made the corrections you pointed out and now it says I have a Syntax error
in the FROM clause. Not sure what I am looking for here.

HubbyMax said:
Thanks. I will give it a try. You are correct. Just for info I am using
Access 2000. The string shoulod result in a product usage by department
withing the request dates grouped by Department. ONce agfain Thanks.

Tom Wickerath said:
Off-hand, it looks like you have an extra space character here, after the
first opening square bracket ( [ )

ON ([ Dept2].Cust2=[Req1].Cust)


Also, this inequality does not look correct to me:

WHERE (([Req1].ReqFilledDate)<=forms![Report Date Range]!BeginDate)

For the beginning date, shouldn't that be >= (greater than or equal to)
instead of <= (less than or equal to)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________


HubbyMax said:
I am setting up an inventory program that requires a monthy billing report
that shows each departments totals for products ordered and what their monthy
charge is. This report must be divided by each department. Currently it says
there is a problem in the "FROM" line but has also said there is problems in
the "INNER JOIN" areas. My Database properties for this form is as follows.
The report is accessed by a date peramiter box;

SELECT DISTINCTROW Products2.StockNu, Products2.ProductName, Dept2.Cust2,
Dept2.DeptName, [Products2].AvgUnitPrice AS [Avg Unit Price],
Sum([Req1].QuantityIssued) AS [Total Units] FROM Products2 INNER JOIN (Dept2
INNER JOIN [Req1]. ON ([ Dept2].Cust2=[Req1].Cust) ON
Products2.StockNu=[Req1].StockNu2) WHERE
(([Req1].ReqFilledDate)<=forms![Report Date Range]!BeginDate) And
([Req1].ReqFilledDate<=forms![Report Date Range]!EndDate) GROUP BY
Products2.StockNu, Products2.ProductName, Dept2.Cust2, Dept2.Name;

Unfortunatly this does not work. My tables are:

Products2, holds all product info
Req1, holds requisition info from departments
Dept2, Info for all departments
Click to expand...
Click to expand...
Click to expand...
 
The very last part of your SQL statement includes the following Group By
clause:

GROUP BY
Products2.StockNu, Products2.ProductName, Dept2.Cust2, Dept2.Name;

Do you really have a field named "Name"? If so, I recommend renaming this
such that you are not using any reserved words. Access MVP Allen Browne
maintains an extensive listing of reserved words here:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I recommend that for anything that you assign a name to within Access, that
you avoid special characters (spaces, #, etc.) and reserved words.

The other possibility is that your Group By clause should have included a
reference to DeptName instead of Name.

Try this SQL statement and see if it doesn't get you a little closer:

PARAMETERS [Forms]![Report Date Range]![BeginDate] DateTime,
[Forms]![Report Date Range]![EndDate] DateTime;
SELECT DISTINCTROW Products2.StockNu, Products2.ProductName, Dept2.Cust2,
Dept2.DeptName, Products2.AvgUnitPrice AS [Avg Unit Price],
Sum([Req1].QuantityIssued) AS [Total Units]
FROM Dept2
INNER JOIN (Products2
INNER JOIN Req1 ON Products2.StockNu = Req1.StockNu2)
ON Dept2.Cust2 = Req1.Cust
WHERE (((Req1.ReqFilledDate)>=[Forms]![Report Date Range]![BeginDate]
And (Req1.ReqFilledDate)=[Forms]![Report Date Range]![EndDate]))
GROUP BY Products2.StockNu, Products2.ProductName,
Dept2.Cust2, Dept2.DeptName, Products2.AvgUnitPrice;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
I replaced the code I was using with yours. It now asks me to "Enter
Perameter Value" DEPT2. Also, I cut and pasted your code but it did leave
large blank spaces. Do I need to enter this code manually? I also want to
conferm that this coded goes in the DATA SORCE portion of the final form it
produces.

Tom Wickerath said:
The very last part of your SQL statement includes the following Group By
clause:

GROUP BY
Products2.StockNu, Products2.ProductName, Dept2.Cust2, Dept2.Name;

Do you really have a field named "Name"? If so, I recommend renaming this
such that you are not using any reserved words. Access MVP Allen Browne
maintains an extensive listing of reserved words here:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I recommend that for anything that you assign a name to within Access, that
you avoid special characters (spaces, #, etc.) and reserved words.

The other possibility is that your Group By clause should have included a
reference to DeptName instead of Name.

Try this SQL statement and see if it doesn't get you a little closer:

PARAMETERS [Forms]![Report Date Range]![BeginDate] DateTime,
[Forms]![Report Date Range]![EndDate] DateTime;
SELECT DISTINCTROW Products2.StockNu, Products2.ProductName, Dept2.Cust2,
Dept2.DeptName, Products2.AvgUnitPrice AS [Avg Unit Price],
Sum([Req1].QuantityIssued) AS [Total Units]
FROM Dept2
INNER JOIN (Products2
INNER JOIN Req1 ON Products2.StockNu = Req1.StockNu2)
ON Dept2.Cust2 = Req1.Cust
WHERE (((Req1.ReqFilledDate)>=[Forms]![Report Date Range]![BeginDate]
And (Req1.ReqFilledDate)=[Forms]![Report Date Range]![EndDate]))
GROUP BY Products2.StockNu, Products2.ProductName,
Dept2.Cust2, Dept2.DeptName, Products2.AvgUnitPrice;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

HubbyMax said:
I made the corrections you pointed out and now it says I have a Syntax error
in the FROM clause. Not sure what I am looking for here.
Click to expand...
Click to expand...
 
I replaced the code I was using with yours. It now asks me to "Enter
Perameter Value" DEPT2.
Click to expand...

I'm not sure why you are getting this parameter, because I did not receive
anything similar. I had created the indicated tables, with the appropriate
fields, and a form ("Report Date Range") with the two text boxes for entering
dates. I didn't have any data in the tables, but the query seemed to run just
fine without producing a parameter prompt.
Also, I cut and pasted your code but it did leave
large blank spaces. Do I need to enter this code manually?
Click to expand...

I did not supply any code; what I provided was a SQL (Structured Query
Language) statement. You could copy and paste that into the SQL View for a
new query.
I also want to confirm that this coded goes in the DATA SORCE portion
of the final form it produces.
Click to expand...

I'm not following you here...


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
For some reason I can not get to your last reply again, I just get a blank
screen. I did creat a Quiry using your suggestion and was able to run it. It
produced a blank table showing the correct rows. I am not sure I am
explaining what I want clearly. I want the data from this to fill in a report
I have created showing the data sorted by department. This report is to be
used as a billing report for each department. This is why I placed it in the
DATA slot in the report properties.

HubbyMax said:
I replaced the code I was using with yours. It now asks me to "Enter
Perameter Value" DEPT2. Also, I cut and pasted your code but it did leave
large blank spaces. Do I need to enter this code manually? I also want to
conferm that this coded goes in the DATA SORCE portion of the final form it
produces.

Tom Wickerath said:
The very last part of your SQL statement includes the following Group By
clause:

GROUP BY
Products2.StockNu, Products2.ProductName, Dept2.Cust2, Dept2.Name;

Do you really have a field named "Name"? If so, I recommend renaming this
such that you are not using any reserved words. Access MVP Allen Browne
maintains an extensive listing of reserved words here:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I recommend that for anything that you assign a name to within Access, that
you avoid special characters (spaces, #, etc.) and reserved words.

The other possibility is that your Group By clause should have included a
reference to DeptName instead of Name.

Try this SQL statement and see if it doesn't get you a little closer:

PARAMETERS [Forms]![Report Date Range]![BeginDate] DateTime,
[Forms]![Report Date Range]![EndDate] DateTime;
SELECT DISTINCTROW Products2.StockNu, Products2.ProductName, Dept2.Cust2,
Dept2.DeptName, Products2.AvgUnitPrice AS [Avg Unit Price],
Sum([Req1].QuantityIssued) AS [Total Units]
FROM Dept2
INNER JOIN (Products2
INNER JOIN Req1 ON Products2.StockNu = Req1.StockNu2)
ON Dept2.Cust2 = Req1.Cust
WHERE (((Req1.ReqFilledDate)>=[Forms]![Report Date Range]![BeginDate]
And (Req1.ReqFilledDate)=[Forms]![Report Date Range]![EndDate]))
GROUP BY Products2.StockNu, Products2.ProductName,
Dept2.Cust2, Dept2.DeptName, Products2.AvgUnitPrice;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

HubbyMax said:
I made the corrections you pointed out and now it says I have a Syntax error
in the FROM clause. Not sure what I am looking for here.
Click to expand...
Click to expand...
Click to expand...
 
I sometimes get the blank screen as well, when using the web portal as you
have been using. Try clicking on the other link which reads "Read the
Response". You can then click on the link that reads "Notify me of replies",
at which point you should see a sign-in dialog.
I did creat a query using your suggestion and was able to run it.
Click to expand...
That's good.
It produced a blank table showing the correct rows.
Click to expand...
Hmmm....a blank table should not have any rows. Did you mean "showing the
correct fields"?
I want the data from this to fill in a report I have created showing
the data sorted by department.
Click to expand...

Okay, then save the query with a name that makes sense (I suggest not using
spaces, special characters or reserved words in this name). Then open the
report in design view, and display the properties dialog for the report.
Select the Data tab. Set the Record Source for the report to the name of the
query that you just saved.

To do the sorting part, click on View | Sorting and Grouping (Access 2003
and earlier--you'll need to hunt around on the ribbon for the same
functionality if you are using Access 2007). Add the Department field to this
dialog. If you want to group by department (rather than just sort) than add a
Group Header and/or Group Footer.

Note: Reports do not "obey" sort orders applied in queries; you must do the
sorting in the Sorting and Grouping dialog, in report design view.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
WE are getting closer. Now after I enter the dates and pree the PREVIEW
button it asks me to enter a parameter for each field on the report. On the
report I have the following

Text box "DepartmentName" control sourse "Cust2"
Text box "ProductName" control sourse "Products2"
Currancy box "AvgUnitPrice" control sourse "Products2"
Number box "TotalUnits" control sourse "Req1"

After tabbing through the parameter requests it loads the form with the
correct start and end dates, and the correct Field titles but no data.
 
WE are getting closer. Now after I enter the dates and press the PREVIEW
button it asks me to enter a parameter for each field on the report.
Click to expand...

You should only get these types of parameter prompts if there is a field
specified in the report that is not present in the Record Source (ie. your
query). With the report open in design view, click on View | Field List to
display a listing of the fields available to the report.

Is the following a typo? You've indicated two text boxes bound to the same
Products2 field:
Text box "ProductName" control source "Products2"
Currency box "AvgUnitPrice" control source "Products2"
Click to expand...

Oh, Eureka! Aren't "Cust2", "Products2" and "Req1" the names of your tables?
You need to set the Control Sources to field names shown in the Field List,
not to the names of tables. So, try setting them as follows:

Text box "DepartmentName" control source "DeptName"
Text box "ProductName" control source "ProductName"
Text box "AvgUnitPrice" control source "Avg Unit Price"
Text box "TotalUnits" control source "Total Units"

By the way, have you installed Service Pack 3 for Access 2000 (since you
indicated previously that you are using this version)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Repeat of previously posted reply
WE are getting closer. Now after I enter the dates and press the PREVIEW
button it asks me to enter a parameter for each field on the report.
Click to expand...

You should only get these types of parameter prompts if there is a field
specified in the report that is not present in the Record Source (ie. your
query). With the report open in design view, click on View | Field List to
display a listing of the fields available to the report.

Is the following a typo? You've indicated two text boxes bound to the same
Products2 field:
Text box "ProductName" control source "Products2"
Currency box "AvgUnitPrice" control source "Products2"
Click to expand...


Oh, Eureka! Aren't "Cust2", "Products2" and "Req1" the names of your tables?
You need to set the Control Sources to field names shown in the Field List,
not to the names of tables. So, try setting them as follows:

Text box "DepartmentName" control source "DeptName"
Text box "ProductName" control source "ProductName"
Text box "AvgUnitPrice" control source "Avg Unit Price"
Text box "TotalUnits" control source "Total Units"


By the way, have you installed Service Pack 3 for Access 2000 (since you
indicated previously that you are using this version)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Eureka is correct!!!!! I made the changes you suggested and all looks to be
OK. I found that the paramentor prompts was mostly due to my sort field
names. I had listed Table names there too. I do not have any data in my some
of the Tables yet so I'm not sure I will get the desired results but at least
I get directly to the form now. Thank you,thank you, THANK YOU!!!!
 
I have discovered that no data is being listed on the report. It does say the
correct starting date and end date but does not list anything that I know is
in the table.
 
Looks like the web interface is back on-line (finally)!
I do not have any data in my some of the Tables yet so I'm not sure I
will get the desired results but at least I get directly to the form now.
Click to expand...

Your SQL statement includes INNER JOIN between the tables. This is also
known as an equi-join. In order for records to be returned with an inner
join, there must be matching records in each of the tables, based on the
joined fields.

You can use outer joins to show all records from one table, and matching
records from another table. Perhaps the easiest way to do this is to switch
your query from SQL View to normal design view. Then aim carefully and
double-click on a join line between two tables. You should see an option for
specifying join type. There are three join types available, with the default
being the equi (inner) join. Generally speaking, if you have the resulting
arrowheads for outer joins pointing away from a central table, you should be
okay. Often times, if you have join arrows pointing towards each other, you
will receive an ambiguous join error message. Play around with the various
join types and see if you can get the correct records returned.
Thank you,thank you, THANK YOU!!!!
Click to expand...

You're very welcome.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Looks like the web interface is back on-line (finally)!


Your SQL statement includes INNER JOIN between the tables. This is also
known as an equi-join. In order for records to be returned with an inner
join, there must be matching records in each of the tables, based on the
joined fields.

You can use outer joins to show all records from one table, and matching
records from another table. Perhaps the easiest way to do this is to switch
your query from SQL View to normal design view. Then aim carefully and
double-click on a join line between two tables. You should see an option for
specifying join type. There are three join types available, with the default
being the equi (inner) join. Generally speaking, if you have the resulting
arrowheads for outer joins pointing away from a central table, you shouldbe
okay. Often times, if you have join arrows pointing towards each other, you
will receive an ambiguous join error message. Play around with the various
join types and see if you can get the correct records returned.


You're very welcome.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________





- Show quoted text -
Click to expand...

I did as you suggested and chamged my table relationships. This
resulted in some data being entered into the report. The proper
Department Name was listed and the proper Product Name was listed. No
Avg Unit Price was listed and the Total Units field contained some
unknown entry "Ou1ber". Also, only the first Department in the Req1
table was shown. There are currently 2 records in that table.
 
Looks like the web interface is back on-line (finally)!


Your SQL statement includes INNER JOIN between the tables. This is also
known as an equi-join. In order for records to be returned with an inner
join, there must be matching records in each of the tables, based on the
joined fields.

You can use outer joins to show all records from one table, and matching
records from another table. Perhaps the easiest way to do this is to switch
your query from SQL View to normal design view. Then aim carefully and
double-click on a join line between two tables. You should see an option for
specifying join type. There are three join types available, with the default
being the equi (inner) join. Generally speaking, if you have the resulting
arrowheads for outer joins pointing away from a central table, you shouldbe
okay. Often times, if you have join arrows pointing towards each other, you
will receive an ambiguous join error message. Play around with the various
join types and see if you can get the correct records returned.


You're very welcome.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________





- Show quoted text -
Click to expand...

Ok, I now have all information showing up on the report but only for
the first record of the first group. It should show all requision data
for all requisions from that group, move to the next group, ext. Also,
in the req1 table each entry I record makes 3 or 4 records in the
table, 1 may have the Department infor with the forst item ordered,
and the next two will show just items ordered. I have been told I need
to table the department info out of the Req1 table as it is alkready
in the Department table and store only the requsition details, items
ordered, in a seperate table. These two tables should be joind in a
common field such as Requsition number. Is this correct?
 
Tell you what....if you can send me a copy of your database (preferably
compacted and zipped), I will take a look at it for you. If interested, send
me a private e-mail message with a valid reply-to address. My e-mail address
is available at the bottom of this page:

http://www.access.qbuilt.com/html/expert_contributors.html#TomW

Scroll down past the two pictures, to the bottom of the page, where you
should
see a clickable link. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/

PS. It looks like the "Notify me of replies" feature is not working (again -
<sigh>), when one makes a post via the web interface, and checks this option.
__________________________________________
 
Back
Top