Jeff,
Sorry if I wasn't clear in my initial description. CustomerID is available
in both the returns table and the order table so to grab the data I need I
will be using customerID and ItemNumber. Also, I'm not interested in
pulling back one record from the order table with the exact Qty although it
may be just one order that does the trick in some cases. What I need to do
is look at orders prior to the return date that match the customer number
and item number combination in the returns table, from there I need to grab
all order records until the order quantities match or exceed the returned
quantity. For example, if customer ABC is returning 50 pieces of item
103-303 I would look in the order table at ABC's purchases of item 103-303
and look at the quantities of those orders. If the first order I come
across for customer ABC has a quantity of 50 pieces for item 103-303 then
that would be the only record I would insert into my temp table and I would
move onto the next item on the returns document if one existed, if not, I
would end the program. However, If I were looking at past orders for
customer ABC's purchases or 103-303 and the first one I came across had a
quantity of 15 pieces, I would want to hold that value of 15 in a variable
and insert that record into my temp table then I would move onto the next
order for ABC where item 103-303 had been purchased. Lets say this order
had a quantity of 25 pieces. I would then add that value to my variable and
insert my record into the temp table. At this point I have two records in
my temp table and my variable equals 40. I now move onto the next order for
customer ABC where item 103-303 had been purchased and this quantity happens
to be 15. I would add this value to my variable and insert the record into
my temp table. Now because my variable or counter is equal to or greater
than (in this case it is greater than) the 50 pieces on the returns document
I would clear out my variable and move onto the next item on the returns
document if one existed, if not, I would end the program. I hope this was
clearer Jeff. Thanks for staying with me.
Chris.
Jeff Boyce said:
Chris
The explanation helps. It sounds like you've adapted your business
process (and rules) to match the application, and have only one customer
per "order" (but no "order" table, just the order detail records/table).
Because we have no way to ensure that the attachment is "safe", you may
not get many folks willing to risk opening an attachment from someone they
don't know.
So, back to the beginning, with this additional clarification ...
If you can only match on ItemNumber and OrderQty, what guarantees do you
have that:
? only one customer ever ordered than quantity of that item, or
? each customer ALWAYS orders a different quantity of each item
I'm thinking that unless you have some other facts to connect the return
to the order, you may not be able to distinguish the above...
And even if you have a Customer (or CustomerNumber), some issues remain.
Unless you can tie the Item & Qty & Customer to a specific OrderNumber,
how will you be sure you have the correct match?
Sorry, but I still seem to need more info...
Regards
Jeff Boyce
Microsoft Office/Access MVP
Chris said:
Yes........that is correct. It is an ERP system so the ability to have
different customers in an order exists. However, that is not how we
choose to use the table. Here at our organization, an order is attached
to just one customer. Attached is a snapshot of our order table layout
with field names. The unit price field isn't on the attachment because I
only did a partial screen capture.
Chris
Jeff Boyce said:
Chris
So, you're (not) saying that you have a separate [Orders] table, that
contains data specific to each order?
You know your situation much better than we do, so we need to ask to
learn
what you already know ...
If each OrderDetail record contains [CustomerID], this implies that each
[ItemNumber] in an order could "belong" to a different customer. Is
that
right?
The reason I'm focusing first on the data structure is because doing
what
you want can be quite difficult if your data isn't well-normalized. MS
Access is a relational database, and its features/functions "expect"
well-normalized data.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Our order table contains multiple records for each order. It is the
order
detail table. A customer could order 15 different items in the same
order. However, each line of detail contains the order number, customer
id, item number, unit price, etc. Does this help Jeff?
Chris
Based on that description of tables/fields, your "Orders" either
consist
ONLY of a single [ItemNumber], or you have multiple records for each
"Order", all sharing the same [OrderNumber].
A more common (though not necessarily more correct) approach is to
have a
SINGLE record in a table of Orders (and hence, a single
[OrderNumber]/id), and use a related (one-to-many) table to store each
order's items.
A bit more info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
Perfect. Here are the fields and tables Jeff.
Returns tbl
RMA_number (this is the number I want them to enter on a form to grab
the returns detail.)
CustomerID (This can link to the CustomerID in the orders table)
ItemNum (this can link to the ItemNum in the orders table)
ItemDesc
ReturnQty
Order tbl ( this is the table that contains the data I need to
insert
into my temp table)
OrderNum
CustomerID
ItemNum
ItemDesc
OrderQty
UnitPrice
Temp tbl
CustomerID
OrderNum
ItemNum
ItemDesc
OrderQty
UnitPrice
Does this info along with my previous post help?
Thanks,
Chris
Chris
The structure of the data. For example, if I were describing a
database used to track enrollments, it might look like:
tblClass
ClassID
ClassTitle
ClassDescription
tblStudent
StudentID
LName
FName
trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate
I'm asking for details about the data because "how" depends on
"what".
Regards
Jeff Boyce
Microsoft Office/Access MVP
We are using PeopleSoft ERP, fromerly, JD Edwards. Our database is
in
SQL. What else whould you like to know Jeff?
Without a clearer picture of your underlying data structure, it's
difficult to guess whether you could accomplish this on a "set"
basis, using a query instead of a loop.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
I'm hoping someone can help me or point me in the right
direction.
I have been asked to create a new process to get historical
pricing
for returned items. The situation:
We will receive a RMA or returns document along with the returned
items. On the returns document we will have customer number, item
number and returned quantity. What needs to happen then is, old
orders need to be checked in the system for each item where the
ordered qty matches the returned qty. The problem is the returns
document could contain quantities that were spread over multiple
orders for one item. Example:
Returns document Old Orders
Item Number Qty Item Number Qty Price
103-133 100 Order 1: 103-133 15
1.00
Order 2: 103-133 50 5.00
Order 3: 103-133 25 3.00
Order 4: 103-133 20 4.00
For each returned item I need to loop through my recordset of old
orders and insert each record of that item purchase until the
order
quantity matches or is greater than the return quantity. Once
this
has occurred, I can move onto the next item in the recordset. I
need to be able to do this until I reach the end of the
recordset.
The reason I have to insert each order for each item until the
order
quantities match or exceed the returned quantity is because each
order record could have different pricing as noted in the example
above. So in the end, for the above example, I would have
inserted
4 records into my temp table for the returned item 103-133. Can
anyone help me accomplish this through code? I've created
recordsets
in the past and I'm familiar with looping, while statements. I'm
just unsure about how I would approach this. Thanks in advance
for
your help.
Chris
