Problem

G

Guest

have a query: as follows

Tables

-Vendors
Id
Name
Description
Code

-Products
Id
Vendor Id
Code
Description
Cost
ReOrder

-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity

Relationships

-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id

Sql View

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;

Here is the problem though.

The query is not grouping like it should be.

What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.

What am i doing wrong?

Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.

thanks

WStoreyII
 
T

Tom Ellison

Dear WStoreyII:

The Need column you have is not the sum of all the needed quantities.
This is likely the cause of your problem. Would it be appropriate to
sum it and not group on it? I'm not sure, but I thought this might be
a goog guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
G

Guest

Tom,

Although your solution did not work it gave me an error, but you are right
that the need column has something to do with it. I went back and cut that
column and it worked fine but i cant find anyway of making it work with the
need column.

Is there any way to create a query like i could use sub queries in the query
and then build my table of of those subqueries?

WStoreyII

thanks for the reply tom

Tom Ellison said:
Dear WStoreyII:

The Need column you have is not the sum of all the needed quantities.
This is likely the cause of your problem. Would it be appropriate to
sum it and not group on it? I'm not sure, but I thought this might be
a goog guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


have a query: as follows

Tables

-Vendors
Id
Name
Description
Code

-Products
Id
Vendor Id
Code
Description
Cost
ReOrder

-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity

Relationships

-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id

Sql View

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;

Here is the problem though.

The query is not grouping like it should be.

What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.

What am i doing wrong?

Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.

thanks

WStoreyII
Click to expand...
Click to expand...
 
G

Guest

Tom,

Although your solution did not work it gave me an error, but you are right
that the need column has something to do with it. I went back and cut that
column and it worked fine but i cant find anyway of making it work with the
need column.

Is there any way to create a query like i could use sub queries in the query
and then build my table of of those subqueries?

WStoreyII

thanks for the reply tom

Tom Ellison said:
Dear WStoreyII:

The Need column you have is not the sum of all the needed quantities.
This is likely the cause of your problem. Would it be appropriate to
sum it and not group on it? I'm not sure, but I thought this might be
a goog guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


have a query: as follows

Tables

-Vendors
Id
Name
Description
Code

-Products
Id
Vendor Id
Code
Description
Cost
ReOrder

-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity

Relationships

-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id

Sql View

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;

Here is the problem though.

The query is not grouping like it should be.

What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.

What am i doing wrong?

Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.

thanks

WStoreyII
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear WStoreyII:

You are going to get more than one row for each Vendor / Product
because you are not aggregating the Need column. This means the query
must display each and every row from the underlying source, because
the ReOrder and Quantity values are coming from there. Are you sure
you don't mean to just show the sum of the Needed values?

Have you tried summing the Need column:

SELECT V.Code AS Vendor, P.Code AS Product,
Sum(I.Quantity) AS [Count], SUM(P.ReOrder) AS ReOrder,
IIf((SUM([ReOrder]) - SUM([Quantity])) <= 0, 0,
SUM([ReOrder]) - SUM([Quantity])) AS Need
FROM tbl_Inventory I
INNER JOIN (tbl_Products P
LEFT JOIN tbl_Vendors V ON P.[Vendor Id] = V.Id)
ON I.[Product Id] = P.Id
GROUP BY V.Code, P.Code
ORDER BY V.Code;

If you do not want a sum of ReOrder or Need then what do you want this
for. If you group by it, then every different value produced for
ReOrder and for Need will create a separate row in the results. This
is actually logical and inevitable.

I'm not here to dictate to you how it must be done, but rather I'm
guessing at what you may have intended. If this isn't what you want,
I suggest you think it through carefully and then try to describe what
you're attempting.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Although your solution did not work it gave me an error, but you are right
that the need column has something to do with it. I went back and cut that
column and it worked fine but i cant find anyway of making it work with the
need column.

Is there any way to create a query like i could use sub queries in the query
and then build my table of of those subqueries?

WStoreyII

thanks for the reply tom

Tom Ellison said:
Dear WStoreyII:

The Need column you have is not the sum of all the needed quantities.
This is likely the cause of your problem. Would it be appropriate to
sum it and not group on it? I'm not sure, but I thought this might be
a goog guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


have a query: as follows

Tables

-Vendors
Id
Name
Description
Code

-Products
Id
Vendor Id
Code
Description
Cost
ReOrder

-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity

Relationships

-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id

Sql View

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;

Here is the problem though.

The query is not grouping like it should be.

What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.

What am i doing wrong?

Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.

thanks

WStoreyII
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Tom,

What i am doing is on the inventory table what it actually does is log where
each item comes from and where it goes. so the products will repeat what i am
attempting to do is to make a query that will show what the actual inventory
is for the date so that the customer may run the query and see what is in
stock.
so on the query i wish to have the following columns

Vendor ProductCode Count ReOrder Need

where need is the number they need to order to be at the reorder rate.

what is aggregate adn why is it messing up this query?

Thanks again for the help i will try your post.

WStoreyII

Tom Ellison said:
Dear WStoreyII:

You are going to get more than one row for each Vendor / Product
because you are not aggregating the Need column. This means the query
must display each and every row from the underlying source, because
the ReOrder and Quantity values are coming from there. Are you sure
you don't mean to just show the sum of the Needed values?

Have you tried summing the Need column:

SELECT V.Code AS Vendor, P.Code AS Product,
Sum(I.Quantity) AS [Count], SUM(P.ReOrder) AS ReOrder,
IIf((SUM([ReOrder]) - SUM([Quantity])) <= 0, 0,
SUM([ReOrder]) - SUM([Quantity])) AS Need
FROM tbl_Inventory I
INNER JOIN (tbl_Products P
LEFT JOIN tbl_Vendors V ON P.[Vendor Id] = V.Id)
ON I.[Product Id] = P.Id
GROUP BY V.Code, P.Code
ORDER BY V.Code;

If you do not want a sum of ReOrder or Need then what do you want this
for. If you group by it, then every different value produced for
ReOrder and for Need will create a separate row in the results. This
is actually logical and inevitable.

I'm not here to dictate to you how it must be done, but rather I'm
guessing at what you may have intended. If this isn't what you want,
I suggest you think it through carefully and then try to describe what
you're attempting.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Although your solution did not work it gave me an error, but you are right
that the need column has something to do with it. I went back and cut that
column and it worked fine but i cant find anyway of making it work with the
need column.

Is there any way to create a query like i could use sub queries in the query
and then build my table of of those subqueries?

WStoreyII

thanks for the reply tom

Tom Ellison said:
Dear WStoreyII:

The Need column you have is not the sum of all the needed quantities.
This is likely the cause of your problem. Would it be appropriate to
sum it and not group on it? I'm not sure, but I thought this might be
a goog guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 20 Oct 2004 19:35:04 -0700, WStoreyII

have a query: as follows

Tables

-Vendors
Id
Name
Description
Code

-Products
Id
Vendor Id
Code
Description
Cost
ReOrder

-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity

Relationships

-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id

Sql View

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;

Here is the problem though.

The query is not grouping like it should be.

What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.

What am i doing wrong?

Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.

thanks

WStoreyII
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Tom,

I got it to work what i had to do was in the need column of the query in
design view i changed it from group by to expression and it worked here is
the sql view

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
[ReOrder]-[Count] AS Need
FROM (tbl_Vendors LEFT JOIN tbl_Products ON tbl_Vendors.Id =
tbl_Products.[Vendor Id]) LEFT JOIN tbl_Inventory ON tbl_Products.Id =
tbl_Inventory.[Product Id]
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder;

Thanks again

WStoreyII

WStoreyII said:
Tom,

What i am doing is on the inventory table what it actually does is log where
each item comes from and where it goes. so the products will repeat what i am
attempting to do is to make a query that will show what the actual inventory
is for the date so that the customer may run the query and see what is in
stock.
so on the query i wish to have the following columns

Vendor ProductCode Count ReOrder Need

where need is the number they need to order to be at the reorder rate.

what is aggregate adn why is it messing up this query?

Thanks again for the help i will try your post.

WStoreyII

Tom Ellison said:
Dear WStoreyII:

You are going to get more than one row for each Vendor / Product
because you are not aggregating the Need column. This means the query
must display each and every row from the underlying source, because
the ReOrder and Quantity values are coming from there. Are you sure
you don't mean to just show the sum of the Needed values?

Have you tried summing the Need column:

SELECT V.Code AS Vendor, P.Code AS Product,
Sum(I.Quantity) AS [Count], SUM(P.ReOrder) AS ReOrder,
IIf((SUM([ReOrder]) - SUM([Quantity])) <= 0, 0,
SUM([ReOrder]) - SUM([Quantity])) AS Need
FROM tbl_Inventory I
INNER JOIN (tbl_Products P
LEFT JOIN tbl_Vendors V ON P.[Vendor Id] = V.Id)
ON I.[Product Id] = P.Id
GROUP BY V.Code, P.Code
ORDER BY V.Code;

If you do not want a sum of ReOrder or Need then what do you want this
for. If you group by it, then every different value produced for
ReOrder and for Need will create a separate row in the results. This
is actually logical and inevitable.

I'm not here to dictate to you how it must be done, but rather I'm
guessing at what you may have intended. If this isn't what you want,
I suggest you think it through carefully and then try to describe what
you're attempting.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Although your solution did not work it gave me an error, but you are right
that the need column has something to do with it. I went back and cut that
column and it worked fine but i cant find anyway of making it work with the
need column.

Is there any way to create a query like i could use sub queries in the query
and then build my table of of those subqueries?

WStoreyII

thanks for the reply tom

:

Dear WStoreyII:

The Need column you have is not the sum of all the needed quantities.
This is likely the cause of your problem. Would it be appropriate to
sum it and not group on it? I'm not sure, but I thought this might be
a goog guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 20 Oct 2004 19:35:04 -0700, WStoreyII

have a query: as follows

Tables

-Vendors
Id
Name
Description
Code

-Products
Id
Vendor Id
Code
Description
Cost
ReOrder

-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity

Relationships

-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id

Sql View

SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;

Here is the problem though.

The query is not grouping like it should be.

What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.

What am i doing wrong?

Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.

thanks

WStoreyII
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining 3 queries 2
Repeat Data 1
how to re-write this Sub to improve the performance? 1
Group By error message 2
*REPOST* tracking inventory using queries 5
select sql query from multiple table 4
Report based on two similar queries 1
Table Design Problem? 1

Top