The whole story - Query problem

  • Thread starter Jason Kaufman via AccessMonster.com
  • Start date
J

Jason Kaufman via AccessMonster.com

ok...here is the sql of what i am working on, maybe this will make more sense
to someone out there...
1) First qry gathers data from the Sales Order files in Mas200
SELECT SO2_SOEntryDetailLine.SalesOrderNumber, SO1_SOEntryHeader.
SalesOrderDate, SO1_SOEntryHeader.ShipExpireDate, SO1_SOEntryHeader.Comment,
SO1_SOEntryHeader.CustomerNumber, SO1_SOEntryHeader.CustomerPONumber,
SO2_SOEntryDetailLine.ItemNumber, SO2_SOEntryDetailLine.QtyOrdered,
SO2_SOEntryDetailLine.QtyShipped, SO2_SOEntryDetailLine.QtyBckordr,
SO2_SOEntryDetailLine.UnitPrice, SO2_SOEntryDetailLine.ChrgAmount,
SO2_SOEntryDetailLine.Extension INTO [SO2 Make Table]
FROM SO2_SOEntryDetailLine INNER JOIN SO1_SOEntryHeader ON
SO2_SOEntryDetailLine.SalesOrderNumber = SO1_SOEntryHeader.SalesOrderNumber;

2) Qry 2 filters the data to only look at items with ship due date after
8/1/05
SELECT [SO Make Table].SalesOrderNumber, [SO Make Table].SalesOrderDate, [SO
Make Table].ShipExpireDate, [SO Make Table].Comment, [SO Make Table].
CustomerNumber, [SO Make Table].CustomerPONumber, [SO Make Table].ItemNumber,
[SO Make Table].QtyOrdered, [SO Make Table].QtyShipped, [QtyOrdered]-
[QtyShipped] AS BOQty, [SO Make Table].QtyBckordr, [SO Make Table].UnitPrice,
[SO Make Table].ChrgAmount, [SO Make Table].Extension
FROM [SO Make Table]
WHERE ((([SO Make Table].ShipExpireDate)>#8/1/2005#) AND (([SO Make Table].
ItemNumber) Is Not Null))
ORDER BY [SO Make Table].ShipExpireDate;

3) Qry 3, looks only at all Sales Orders with due date of 11/1/05 or less
SELECT [SO strain].ItemNumber, [SO strain].BOQty, [SO strain].SalesOrderDate,
[SO strain].ShipExpireDate INTO [qry 2 table]
FROM [SO strain]
GROUP BY [SO strain].ItemNumber, [SO strain].BOQty, [SO strain].
SalesOrderDate, [SO strain].ShipExpireDate
HAVING ((([SO strain].ShipExpireDate)<#11/1/2005#))
ORDER BY [SO strain].ItemNumber;

4) This qry, summarizes all the Sales Order quantities that have yet to ship
(with due date of 11/1/05 or less)
SELECT [qry 2 table].ItemNumber, Sum([qry 2 table].BOQty) AS SumOfQtyBO INTO
[qry 3 table]
FROM [qry 2 table]
GROUP BY [qry 2 table].ItemNumber
ORDER BY [qry 2 table].ItemNumber DESC;

5) This qry, takes the results of qry 4 (make table) and takes the SumOfQtyBO
and subtracts it from QtyOnHand to make a new OnHand quantity (this quantity
is based on the theory that everything before the desired date range will
have shipped) and then pushes all the items from previous qry into entire
inventory file, and since qry file is smaller, i had to makes sure a value of
0 was put in instead of null value.
SELECT IM1_InventoryMasterfile.ItemNumber, IM2_InventoryItemWhseDetl.
QtyOnHand, IIf([SumOfQtyBO] Is Null,0,[SumOfQtyBO]) AS BO, [QtyOnHand]-IIf(
[SumOfQtyBO] Is Null,0,[SumOfQtyBO]) AS ReviseOH INTO [qry 4 table]
FROM ([qry 3 table] RIGHT JOIN IM1_InventoryMasterfile ON [qry 3 table].
ItemNumber = IM1_InventoryMasterfile.ItemNumber) INNER JOIN
IM2_InventoryItemWhseDetl ON IM1_InventoryMasterfile.ItemNumber =
IM2_InventoryItemWhseDetl.ItemNumber
WHERE (((IM2_InventoryItemWhseDetl.WhseCode)="000"));

6) This qry draws out the Revised OH qry from previous query within the date
range of 1/1/2005 and 2/1/06 (after I get this mess done, I will be using
'between [start] and [end]'.
SELECT [qry 4 table].ItemNumber, [qry 4 table].ReviseOH, [SO Make Table].
ShipExpireDate INTO [qry 5 table]
FROM [SO Make Table] INNER JOIN [qry 4 table] ON [SO Make Table].ItemNumber =
[qry 4 table].ItemNumber
GROUP BY [qry 4 table].ItemNumber, [qry 4 table].ReviseOH, [SO Make Table].
ShipExpireDate
HAVING ((([SO Make Table].ShipExpireDate) Between #11/1/2005# And #2/1/2006#))

ORDER BY [qry 4 table].ItemNumber DESC;

From here out, I have queries, but ultimately its doesnt get my desired
result.

7)
SELECT [qry 5 table].ItemNumber, [qry 2 table].BOQty INTO [qry 6 table]
FROM [qry 2 table] INNER JOIN [qry 5 table] ON [qry 2 table].ItemNumber =
[qry 5 table].ItemNumber
GROUP BY [qry 5 table].ItemNumber, [qry 2 table].BOQty
ORDER BY [qry 5 table].ItemNumber DESC;

What I want to have happen is to look at the between dates, show the Revised
OH quantity from 5) and then for each item number within a sales order,
subtract the sales order quantity and have a new revised OnHand quantity.
This quantity will then need to be carrried over to the next incident of the
item number and the same process would occur again.
I sort of got this result except for the revised OnHand quantity changing for
each time an item number appeared. I could only get the listing of all the
item numbers with there subsquent sales order quantity and due date but I
could not determine how to link/match them up.

So.
This is my project given to me by my boss in the first week of working here
and now its been 4 weeks since being here and he wants to know if this is
possible. I believe it is, but I just dont know enough to figure it out at
the moment. Apparenlty he had a guy working on a Crystal report for this
same result and he had been working on it for 6 months with no result. I
dont know crystal since where i worked before they didnt know crystal reports
for Mas200 and so used Access instead.

Anyways, if anyone would like to tackle this monster, I would truly be
grateful !!!
Jason
 
J

Jason Kaufman via AccessMonster.com

DOES ANYONE HAVE ANY THOUGHTS ON THIS?????????????

Jason said:
ok...here is the sql of what i am working on, maybe this will make more sense
to someone out there...
1) First qry gathers data from the Sales Order files in Mas200
SELECT SO2_SOEntryDetailLine.SalesOrderNumber, SO1_SOEntryHeader.
SalesOrderDate, SO1_SOEntryHeader.ShipExpireDate, SO1_SOEntryHeader.Comment,
SO1_SOEntryHeader.CustomerNumber, SO1_SOEntryHeader.CustomerPONumber,
SO2_SOEntryDetailLine.ItemNumber, SO2_SOEntryDetailLine.QtyOrdered,
SO2_SOEntryDetailLine.QtyShipped, SO2_SOEntryDetailLine.QtyBckordr,
SO2_SOEntryDetailLine.UnitPrice, SO2_SOEntryDetailLine.ChrgAmount,
SO2_SOEntryDetailLine.Extension INTO [SO2 Make Table]
FROM SO2_SOEntryDetailLine INNER JOIN SO1_SOEntryHeader ON
SO2_SOEntryDetailLine.SalesOrderNumber = SO1_SOEntryHeader.SalesOrderNumber;

2) Qry 2 filters the data to only look at items with ship due date after
8/1/05
SELECT [SO Make Table].SalesOrderNumber, [SO Make Table].SalesOrderDate, [SO
Make Table].ShipExpireDate, [SO Make Table].Comment, [SO Make Table].
CustomerNumber, [SO Make Table].CustomerPONumber, [SO Make Table].ItemNumber,
[SO Make Table].QtyOrdered, [SO Make Table].QtyShipped, [QtyOrdered]-
[QtyShipped] AS BOQty, [SO Make Table].QtyBckordr, [SO Make Table].UnitPrice,
[SO Make Table].ChrgAmount, [SO Make Table].Extension
FROM [SO Make Table]
WHERE ((([SO Make Table].ShipExpireDate)>#8/1/2005#) AND (([SO Make Table].
ItemNumber) Is Not Null))
ORDER BY [SO Make Table].ShipExpireDate;

3) Qry 3, looks only at all Sales Orders with due date of 11/1/05 or less
SELECT [SO strain].ItemNumber, [SO strain].BOQty, [SO strain].SalesOrderDate,
[SO strain].ShipExpireDate INTO [qry 2 table]
FROM [SO strain]
GROUP BY [SO strain].ItemNumber, [SO strain].BOQty, [SO strain].
SalesOrderDate, [SO strain].ShipExpireDate
HAVING ((([SO strain].ShipExpireDate)<#11/1/2005#))
ORDER BY [SO strain].ItemNumber;

4) This qry, summarizes all the Sales Order quantities that have yet to ship
(with due date of 11/1/05 or less)
SELECT [qry 2 table].ItemNumber, Sum([qry 2 table].BOQty) AS SumOfQtyBO INTO
[qry 3 table]
FROM [qry 2 table]
GROUP BY [qry 2 table].ItemNumber
ORDER BY [qry 2 table].ItemNumber DESC;

5) This qry, takes the results of qry 4 (make table) and takes the SumOfQtyBO
and subtracts it from QtyOnHand to make a new OnHand quantity (this quantity
is based on the theory that everything before the desired date range will
have shipped) and then pushes all the items from previous qry into entire
inventory file, and since qry file is smaller, i had to makes sure a value of
0 was put in instead of null value.
SELECT IM1_InventoryMasterfile.ItemNumber, IM2_InventoryItemWhseDetl.
QtyOnHand, IIf([SumOfQtyBO] Is Null,0,[SumOfQtyBO]) AS BO, [QtyOnHand]-IIf(
[SumOfQtyBO] Is Null,0,[SumOfQtyBO]) AS ReviseOH INTO [qry 4 table]
FROM ([qry 3 table] RIGHT JOIN IM1_InventoryMasterfile ON [qry 3 table].
ItemNumber = IM1_InventoryMasterfile.ItemNumber) INNER JOIN
IM2_InventoryItemWhseDetl ON IM1_InventoryMasterfile.ItemNumber =
IM2_InventoryItemWhseDetl.ItemNumber
WHERE (((IM2_InventoryItemWhseDetl.WhseCode)="000"));

6) This qry draws out the Revised OH qry from previous query within the date
range of 1/1/2005 and 2/1/06 (after I get this mess done, I will be using
'between [start] and [end]'.
SELECT [qry 4 table].ItemNumber, [qry 4 table].ReviseOH, [SO Make Table].
ShipExpireDate INTO [qry 5 table]
FROM [SO Make Table] INNER JOIN [qry 4 table] ON [SO Make Table].ItemNumber =
[qry 4 table].ItemNumber
GROUP BY [qry 4 table].ItemNumber, [qry 4 table].ReviseOH, [SO Make Table].
ShipExpireDate
HAVING ((([SO Make Table].ShipExpireDate) Between #11/1/2005# And #2/1/2006#))

ORDER BY [qry 4 table].ItemNumber DESC;

From here out, I have queries, but ultimately its doesnt get my desired
result.

7)
SELECT [qry 5 table].ItemNumber, [qry 2 table].BOQty INTO [qry 6 table]
FROM [qry 2 table] INNER JOIN [qry 5 table] ON [qry 2 table].ItemNumber =
[qry 5 table].ItemNumber
GROUP BY [qry 5 table].ItemNumber, [qry 2 table].BOQty
ORDER BY [qry 5 table].ItemNumber DESC;

What I want to have happen is to look at the between dates, show the Revised
OH quantity from 5) and then for each item number within a sales order,
subtract the sales order quantity and have a new revised OnHand quantity.
This quantity will then need to be carrried over to the next incident of the
item number and the same process would occur again.
I sort of got this result except for the revised OnHand quantity changing for
each time an item number appeared. I could only get the listing of all the
item numbers with there subsquent sales order quantity and due date but I
could not determine how to link/match them up.

So.
This is my project given to me by my boss in the first week of working here
and now its been 4 weeks since being here and he wants to know if this is
possible. I believe it is, but I just dont know enough to figure it out at
the moment. Apparenlty he had a guy working on a Crystal report for this
same result and he had been working on it for 6 months with no result. I
dont know crystal since where i worked before they didnt know crystal reports
for Mas200 and so used Access instead.

Anyways, if anyone would like to tackle this monster, I would truly be
grateful !!!
Jason
Click to expand...
 
K

Ken Snell [MVP]

Quite honestly, posting a lot of SQL statements and asking us how to
fix/change them is not going to lead to a speedy answer from us volunteers.

May I suggest that you start with a statement of your table structures, and
then what data you're storing in the tables, and what you want to achieve
with those data? Help us to see what you have and what you want to do with
it. Then the SQL statements can be viewed with an understanding of what
you're wanting to accomplish.

--

Ken Snell
<MS ACCESS MVP>


Jason Kaufman via AccessMonster.com said:
DOES ANYONE HAVE ANY THOUGHTS ON THIS?????????????

Jason said:
ok...here is the sql of what i am working on, maybe this will make more
sense
to someone out there...
1) First qry gathers data from the Sales Order files in Mas200
SELECT SO2_SOEntryDetailLine.SalesOrderNumber, SO1_SOEntryHeader.
SalesOrderDate, SO1_SOEntryHeader.ShipExpireDate,
SO1_SOEntryHeader.Comment,
SO1_SOEntryHeader.CustomerNumber, SO1_SOEntryHeader.CustomerPONumber,
SO2_SOEntryDetailLine.ItemNumber, SO2_SOEntryDetailLine.QtyOrdered,
SO2_SOEntryDetailLine.QtyShipped, SO2_SOEntryDetailLine.QtyBckordr,
SO2_SOEntryDetailLine.UnitPrice, SO2_SOEntryDetailLine.ChrgAmount,
SO2_SOEntryDetailLine.Extension INTO [SO2 Make Table]
FROM SO2_SOEntryDetailLine INNER JOIN SO1_SOEntryHeader ON
SO2_SOEntryDetailLine.SalesOrderNumber =
SO1_SOEntryHeader.SalesOrderNumber;

2) Qry 2 filters the data to only look at items with ship due date after
8/1/05
SELECT [SO Make Table].SalesOrderNumber, [SO Make Table].SalesOrderDate,
[SO
Make Table].ShipExpireDate, [SO Make Table].Comment, [SO Make Table].
CustomerNumber, [SO Make Table].CustomerPONumber, [SO Make
Table].ItemNumber,
[SO Make Table].QtyOrdered, [SO Make Table].QtyShipped, [QtyOrdered]-
[QtyShipped] AS BOQty, [SO Make Table].QtyBckordr, [SO Make
Table].UnitPrice,
[SO Make Table].ChrgAmount, [SO Make Table].Extension
FROM [SO Make Table]
WHERE ((([SO Make Table].ShipExpireDate)>#8/1/2005#) AND (([SO Make
Table].
ItemNumber) Is Not Null))
ORDER BY [SO Make Table].ShipExpireDate;

3) Qry 3, looks only at all Sales Orders with due date of 11/1/05 or less
SELECT [SO strain].ItemNumber, [SO strain].BOQty, [SO
strain].SalesOrderDate,
[SO strain].ShipExpireDate INTO [qry 2 table]
FROM [SO strain]
GROUP BY [SO strain].ItemNumber, [SO strain].BOQty, [SO strain].
SalesOrderDate, [SO strain].ShipExpireDate
HAVING ((([SO strain].ShipExpireDate)<#11/1/2005#))
ORDER BY [SO strain].ItemNumber;

4) This qry, summarizes all the Sales Order quantities that have yet to
ship
(with due date of 11/1/05 or less)
SELECT [qry 2 table].ItemNumber, Sum([qry 2 table].BOQty) AS SumOfQtyBO
INTO
[qry 3 table]
FROM [qry 2 table]
GROUP BY [qry 2 table].ItemNumber
ORDER BY [qry 2 table].ItemNumber DESC;

5) This qry, takes the results of qry 4 (make table) and takes the
SumOfQtyBO
and subtracts it from QtyOnHand to make a new OnHand quantity (this
quantity
is based on the theory that everything before the desired date range will
have shipped) and then pushes all the items from previous qry into entire
inventory file, and since qry file is smaller, i had to makes sure a value
of
0 was put in instead of null value.
SELECT IM1_InventoryMasterfile.ItemNumber, IM2_InventoryItemWhseDetl.
QtyOnHand, IIf([SumOfQtyBO] Is Null,0,[SumOfQtyBO]) AS BO,
[QtyOnHand]-IIf(
[SumOfQtyBO] Is Null,0,[SumOfQtyBO]) AS ReviseOH INTO [qry 4 table]
FROM ([qry 3 table] RIGHT JOIN IM1_InventoryMasterfile ON [qry 3 table].
ItemNumber = IM1_InventoryMasterfile.ItemNumber) INNER JOIN
IM2_InventoryItemWhseDetl ON IM1_InventoryMasterfile.ItemNumber =
IM2_InventoryItemWhseDetl.ItemNumber
WHERE (((IM2_InventoryItemWhseDetl.WhseCode)="000"));

6) This qry draws out the Revised OH qry from previous query within the
date
range of 1/1/2005 and 2/1/06 (after I get this mess done, I will be using
'between [start] and [end]'.
SELECT [qry 4 table].ItemNumber, [qry 4 table].ReviseOH, [SO Make Table].
ShipExpireDate INTO [qry 5 table]
FROM [SO Make Table] INNER JOIN [qry 4 table] ON [SO Make
Table].ItemNumber =
[qry 4 table].ItemNumber
GROUP BY [qry 4 table].ItemNumber, [qry 4 table].ReviseOH, [SO Make
Table].
ShipExpireDate
HAVING ((([SO Make Table].ShipExpireDate) Between #11/1/2005# And
#2/1/2006#))

ORDER BY [qry 4 table].ItemNumber DESC;

From here out, I have queries, but ultimately its doesnt get my desired
result.

7)
SELECT [qry 5 table].ItemNumber, [qry 2 table].BOQty INTO [qry 6 table]
FROM [qry 2 table] INNER JOIN [qry 5 table] ON [qry 2 table].ItemNumber =
[qry 5 table].ItemNumber
GROUP BY [qry 5 table].ItemNumber, [qry 2 table].BOQty
ORDER BY [qry 5 table].ItemNumber DESC;

What I want to have happen is to look at the between dates, show the
Revised
OH quantity from 5) and then for each item number within a sales order,
subtract the sales order quantity and have a new revised OnHand quantity.
This quantity will then need to be carrried over to the next incident of
the
item number and the same process would occur again.
I sort of got this result except for the revised OnHand quantity changing
for
each time an item number appeared. I could only get the listing of all
the
item numbers with there subsquent sales order quantity and due date but I
could not determine how to link/match them up.

So.
This is my project given to me by my boss in the first week of working
here
and now its been 4 weeks since being here and he wants to know if this is
possible. I believe it is, but I just dont know enough to figure it out
at
the moment. Apparenlty he had a guy working on a Crystal report for this
same result and he had been working on it for 6 months with no result. I
dont know crystal since where i worked before they didnt know crystal
reports
for Mas200 and so used Access instead.

Anyways, if anyone would like to tackle this monster, I would truly be
grateful !!!
Jason
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Ken posted a request for the DDL that creates your tables. I'll
second that call, but will settle for excellent descriptions of them.

Sample data for each table is good. 5-10 rows of critical data (in
comma delimited rows) is usually enough.

And, best of all, a list of "expected output", data that looks like
you want it to look once the appropriate query is complete.
Click to expand...


I'm not seeking DDL, but a word description and field name lists, etc.
 
C

Chris2

Jason Kaufman via AccessMonster.com said:
DOES ANYONE HAVE ANY THOUGHTS ON THIS?????????????


Jason
Click to expand...

Jason,

Well, ok . . .

I had some stuff written yesterday. When I realized I had no idea
what you were talking about when it started appearing to me that you
were taking duplicate actions further along the sequence, and I
couldn't figure out why, I stopped trying to create a working answer
to your question, and I started to give some general advice. When I
had written a little bit of that, I stopped (because usually so many
jump in here with that sort of stuff).

I'll just copy it below

--------------------------------------------------------------

First, since you appear to be deveoping this:

1) It is a good idea to avoid the use spaces in object names. See the
third commandment http://www.mvps.org/access/tencommandments.htm, and
the other ones are good, too). Also, queries and tables named "qry
3", etc., contain no obvious meaning. You wrote them, and have an
idea of what the step numbers represent. To me, they mean 1, 2, 3,
etc. Six months after you finish up with them and return for a little
tweaking, you won't know what the object names mean, either. Try
re-naming tables along for the data they contain, and queries for the
actions they take.

2) You are having a tough time because you are importing data that is
not normalized and then are using MS Access to westle with it in an
unnormalized state.

My recommendations are to take a look at the data that you are
importing. Reorganize the import so that the data winds up
normalized. (Google: Normalization 1NF 2NF 3NF). Yes, you are going
to have to do some studying to learn what this is (if you already
know, please forgive my presumption).

Note: SQL is not very good at looking at "the next row" unless you
have somehow sequentially numbered them and "the next highest number"
is always assigned to the next row you happen to want to be dealing
with (and there is very little in the way of good processes to assure
this that don't involve more contortions than someone trying to open
an M'azing candy bar).

Ken posted a request for the DDL that creates your tables. I'll
second that call, but will settle for excellent descriptions of them.

Sample data for each table is good. 5-10 rows of critical data (in
comma delimited rows) is usually enough.

And, best of all, a list of "expected output", data that looks like
you want it to look once the appropriate query is complete.

When posting SQL, please strip out all non-relevant columns for
brevity.


Sincerely,

Chris O.
 
C

Chris2

Ken Snell said:
I'm not seeking DDL, but a word description and field name lists, etc.
Click to expand...

Ken,

I apologize. I did misrepresent what you wrote. I read too much into
it.


Sincerely,

Chris O.
 

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

Slow Query 3
Database problem 1
Let MS Access do the writing/printing 2
Problem editing a query based on another query and table 3
cumulative total query 1
Query to display highest totals 1
qry difficulty 2
calculation between 2 records 2

Top