Can a report display a new order number and older order details?

[DanicaDear]

Hello again Bytes...I missed you!

First, background:
In a hotstick lab, we ship orders every two years. We ship a new order and the customer uses the new box to return the previous year's order. For example, in November 2007 we shipped an order. November 2009 those sticks expire, and we ship a new order (because customers can't be without sticks) and when they receive the new 2009 order they ship back the old 2007 order.

A box number and ship date are put in the DB when the each order is shipped. When that box comes back, hopefully in about 30 days, the box_return_date is put into the system. This allows me to use my report which is based on a parameter query “More than xxx days since new shipment.” If I enter 60, it lets me know if any box (containing old order sticks) hasn’t been returned 60 days out from the new order shipment (using criteria of box_return_date =Null and now() - ship date > 60).

Problem: Because the box number and dates are attached to the new order, the order details show the new order. But since I’m looking for sticks to be returned from the previous order, I actually want to show the old order’s details. So I want the order number from the new order and the order details from the previous order (all connected by a single customer number). It might even be more clear if the report showed both old and new order number and details. Keep in mind they would have order in 2005,2003, etc that I would *not* want to show.

If you can offer any guidance, I would certainly appreciate it. I have thought intensely about this for 5 hours now and my limited VBA skills are preventing me from coming up with a solution. Can anyone help?

[MMcCarthy]

I think I understand what you are asking. Does the following make any sense to you.

tblShipment

ShipID (PK)
CustID (FK)
ShipDate
RefNum

Query

SELECT T1.ShipID As NewShipment, T1.CustID As Customer, 
T1.ShipDate As NewShipDate, T1.RefNum As NewRef, 
T2.ShipID As OldShipment, T2.ShipDate As OldShipDate, 
T2.RefNum As OldRef 
FROM tblShipment As T1 INNER JOIN tblShipment As T2
ON T1.CustID = T2.CustID
AND Year(T2.ShipDate) = Year(T1.ShipDate) - 2

[DanicaDear]

It barely makes sense, LOL.
I can read it and get an idea what you are trying to do.

The code you have provided looks a little different than what I'm used to...so I'm going to guess it's a SQL statement. ??
Where do I put it? If it's through the properties box in the report (where it links to the query), I know where. But if you put the code directly in the query itself, I don't know where it goes.

I'm off til Monday and I won't try it til then. Thanks so very much for your time.

[DanicaDear]

One problem I might be able to see (with my inexperienced eye) is in line 7 where you have year minus 2.
Consider this: January 2007 we ship an order. Sticks expire in January 2009 so we would likely ship the new order in December 2008. Now we have a year minus 1.

If it helps, the ShipID is 2007XXXX (XXXX=consecuti ve numbers beginning with 0001) and 2008XXXX, etc, so the year is also part of the ShipID. Could that be beneficial?

[MMcCarthy]

Tell me the actual table name and field names and I'll try to put something together for Monday for you.

If you open a new query (don't add any tables) in design view and then change the view to SQL you can paste in the query as I've given it to you. The table names and field names won't match at the moment though.

Mary

[NeoPa]

Hi again Danica.

It seems that you need to have a design that supports your requirement first. Before you even consider coding it up in VBA (Actually you need to consider the queries - SQL - before any VBA too). I believe this is what Mary (MSquared) was about first with her table layout (which is basically what you should have in your database).

Although the SQL makes sense, I would suggest the SQL be more like :

SELECT   tS.ShipID As NewShipment,
         tS.CustID As Customer,
         tS.ShipDate As NewShipDate,
         tS.RefNum As NewRef,
         Max(tS2.ShipID) As OldShipment,
         Max(tS2.ShipDate) As OldShipDate,
         Max(tS2.RefNum) As OldRef 

FROM     tblShipment As tS INNER JOIN
         tblShipment As tS2
  ON     (tS.CustID=tS2.CustID)
 AND     (tS.ShipDate>tS2.ShipDate)

WHERE    tS.ShipDate>=DateAdd('d',60,Date())

GROUP BY tS.CustID,
         tS.ShipID,
         tS.ShipDate,
         tS.RefNum

[MMcCarthy]

Although the SQL makes sense, I would suggest the SQL be more like :

SELECT   tS.ShipID As NewShipment,
         tS.CustID As Customer,
         tS.ShipDate As NewShipDate,
         tS.RefNum As NewRef,
         Max(tS2.ShipID) As OldShipment,
         Max(tS2.ShipDate) As OldShipDate,
         Max(tS2.RefNum) As OldRef 

FROM     tblShipment As tS INNER JOIN
         tblShipment As tS2
  ON     (tS.CustID=tS2.CustID)
 AND     (tS.ShipDate>tS2.ShipDate)

WHERE    tS.ShipDate>=DateAdd('d',60,Date())

GROUP BY tS.CustID,
         tS.ShipID,
         tS.ShipDate,
         tS.RefNum

I agree with Ade here Danica as it's a much cleaner solution and mine I fear was a quick mockup :D

Mary

[DanicaDear]

Thanks for your help everyone. I'm still trying to digest and I know I'll get it. You all are so patient. (thankfully.)

First, I am pasting my current query's SQL, just in case it helps. Keep in mind this is the query that is working but not showing me the info I want to see.

PARAMETERS [Days Since Last Shipment] Short;
SELECT DISTINCT HOTSTICK_ORDERS.ORDER_NUM, HOTSTICK_ORDERS.CUST_NUM, HOTSTICK_ORDERS.DATE_SHIP, HOTSTICK_ORDERS.BOX_NUM, HOTSTICK_ORDERS.REMARKS, Date()-[DATE_SHIP] AS DAYS_SINCE_LAST_SHIP
FROM (HOTSTICK_CUSTOMERS INNER JOIN HOTSTICK_ORDERS ON HOTSTICK_CUSTOMERS.CUST_NUM = HOTSTICK_ORDERS.CUST_NUM) INNER JOIN HOTSTICK_ORDER_DETAILS ON HOTSTICK_ORDERS.ORDER_NUM = HOTSTICK_ORDER_DETAILS.ORDER_NUM
WHERE (((Date()-[DATE_SHIP])>=[Days Since Last Shipment]) AND ((HOTSTICK_ORDERS.DATE_RET) Is Null))
ORDER BY Date()-[DATE_SHIP] DESC;

Now, I'm taking NeoPa's suggestion and replacing it with my real table/field names.

SELECT   tS.ORDER_NUM As NewShipment, 
         tS.CUST_NUM As Customer, 
         tS.DATE_SHIP As NewShipDate, 
         tS.BOX_NUM As NewRef, 
         Max(tS2.ORDER_NUM) As OldShipment, 
         Max(tS2.DATE_SHIP) As OldShipDate, 
         Max(tS2.BOX_NUM) As OldRef  
  
FROM     HOTSTICK_ORDERS As tS INNER JOIN 
         HOTSTICK_ORDERS As tS2 
  ON     (tS.CUST_NUM=tS2.CUST_NUM) 
 AND     (tS.DATE_SHIP>tS2.DATE_SHIP) 
  
WHERE    tS.DATE_SHIP>=DateAdd('d',[Days Since Last Shipment],Date()) 
  
GROUP BY tS.CUST_NUM, 
         tS.ORDER_NUM, 
         tS.DATE_SHIP, 
         tS.BOX_NUM

I wasn't exactly sure what you referred to as RefNum but I'm assuming it's the box number. If not, please advice. The only other field I have in the table besides BOX_NUM is DATE_RET. (DATE_RET (*date returned*) should be a null field if the record shows up in the query.) I haven't seen DATE_RET anywhere in the SQL statement. Does it need to be?

Please note my use of the parameter name in the WHERE line, Line 14. I hope this is ok.

I'm not sure if I should totally wipe out my current SQL statement and paste in NeoPa's. Is that what you are advising? I have backed up my DB in case I screw up. I can be dangerous. LOL.

Also, what is tS? Maybe that will help me understand better what guys are doing.

Thanks so much. I cannot thank you enough. My debt to Bytes is increasing way too fast!!! I scoped out the questions to try to pay back...but ummm, apparently not ready yet. LOL.

[NeoPa]

Firstly Danica, everything in a query depends on the tables you're using in the query. As you haven't yet posted this we are having to guess somewhat as to what you might have. Mary showed the sort of things she expected you to need and I built upon that. I suggest before we go much further you post your table layout (often referred to as Meta-Data) in here for us to see. I include the body of a post I often use to help members to post this :

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

[I]Field           Type      IndexInfo[/I]
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

If you have tables with oodles of fields you can decide to include only those you think are involved. Obviously err on the side of including if you're not sure.

Now, I'm taking NeoPa's suggestion and replacing it with my real table/field names.

SELECT   tS.ORDER_NUM As NewShipment, 
         tS.CUST_NUM As Customer, 
         tS.DATE_SHIP As NewShipDate, 
         tS.BOX_NUM As NewRef, 
         Max(tS2.ORDER_NUM) As OldShipment, 
         Max(tS2.DATE_SHIP) As OldShipDate, 
         Max(tS2.BOX_NUM) As OldRef  
  
FROM     HOTSTICK_ORDERS As tS INNER JOIN 
         HOTSTICK_ORDERS As tS2 
  ON     (tS.CUST_NUM=tS2.CUST_NUM) 
 AND     (tS.DATE_SHIP>tS2.DATE_SHIP) 
  
WHERE    tS.DATE_SHIP>=DateAdd('d',[Days Since Last Shipment],Date()) 
  
GROUP BY tS.CUST_NUM, 
         tS.ORDER_NUM, 
         tS.DATE_SHIP, 
         tS.BOX_NUM

This is helpful to see what fields you have available. Meta-data is better, but this gives us some good info anyway.

I wasn't exactly sure what you referred to as RefNum but I'm assuming it's the box number. If not, please advice. The only other field I have in the table besides BOX_NUM is DATE_RET. (DATE_RET (*date returned*) should be a null field if the record shows up in the query.) I haven't seen DATE_RET anywhere in the SQL statement. Does it need to be?

This is due to our not having any idea what fields we had to work with. As mentionned above, Mary posted an idea about what you need to have as a basic, and I simply worked from that.

Please note my use of the parameter name in the WHERE line, Line 14. I hope this is ok.

This is perfectly fine. It's much better when you take on what we post and suit it to your needs. Any signs of learning or progress is always well appreciated.

I would point out though, that this seems to be a prompt for the operator to fill in a value, whereas it is more usual to have a value entered on a form for all to see, then use that value.

I'm not sure if I should totally wipe out my current SQL statement and paste in NeoPa's. Is that what you are advising? I have backed up my DB in case I screw up. I can be dangerous. LOL.

The time for that is when you have the code working perfectly. That way you already know you won't need your old code any more. In the mean-time store it in a query with a different name to refer back to if and when required.

Also, what is tS? Maybe that will help me understand better what guys are doing.

tS is an ALIAS. Look on lines #9 & #10 of the SQL in your post. The AS keyword means to treat the word following as the name of the item preceeding.

HOTSTICK_ORDERS AS tS

This enables you to refer the the table HOTSTICK_ORDERS anywhere in your SQL as tS (except in this statement of course. tS AS tS would not work). It's like a short-hand to save you time and keyboard strokes. I also find such SQL much easier to understand and digest.

PS. were I to choose a short name for SQL use, for HOTSTICK_ORDERS , I would choose something like :
tHO - Standing for table HOTSTICK_ORDERS.

[DanicaDear]

While I'm digesting/investigating/trying the rest.....see if this is what you've asked of me:

(NeoPa, I tried using the spaces but when I did Preview Post it had removed them all. So I tried to use dashes instead.) Ah hah..I've learned something else....I just edited it using CODE TAGS. Don't write me a ticket if you saw it before I edited it, NeoPa. ;-) It's still not lining up nicely as it is in my post window.

Table [B]HOTSTICK_ORDERS[/B]
(This is where I seek to call up the info.)
ORDER_NUM  Text        PK
CUST_NUM   CUST_NUM
DATE_SHIP  Date/Time
BOX_NUM    Text
DATE_RET   Date/Time
REMARKS    Text

Table [B]HOTSTICK_ORDER_DETAILS[/B]
(This is the info I want to show if an order gets called up.)
ORDER_NUM       Text    PK in this table. FK for above table
QTY_SHIPPED     Number
COMMODITY_DESC  Text    PK
QTY_RETURNED    Number
QTY_ABUSED      Number
CHARGED_OUT     Yes/No
REMARKS         Text

[NeoPa]

It lines up perfectly. What are you talking about?

The trick is to edit it in a text editor. Typically these use non-proportional fonts (EG. Courier; Courier New) which ensures each character takes the same amount of horizontal space no matter if it's a 'W' or an 'i'.

This way it is easy to ensure all is lined up smartly - and easier to read because of it.

BTW I was just kidding. I sorted yours out for you of course.

[topher23]

I'm wondering about the purpose behind having a separate Order and Order Detail table. If each Order has a single, one-to-one relationship with an Order Detail (which is my assumption), wouldn't it be easier to use a single Order table?

If the tables were merged (making this easier), I think I'd just use a subreport to pull the last detail for that customer previous to the date of the new shipment. The subreport would have a recordsource something like

SELECT TOP 1 HOTSTICK_ORDERS.* FROM HOTSTICK_ORDERS WHERE DATE_SHIP < Reports!ParentReport!DATE_SHIP AND CUST_NUM =  Reports!ParentReport!CUST_NUM ORDER BY DATE_SHIP DESC;

With two tables, you'd have to do a JOIN in the SQL, like this:

SELECT TOP 1 HOTSTICK_ORDERS.*, HOTSTICK_ORDER_DETAILS.* FROM HOTSTICK_ORDERS INNER JOIN HOTSTICK_ORDER_DETAILS ON HOTSTICK_ORDERS.ORDER_NUM = HOTSTICK_ORDER_DETAILS.ORDER_NUM WHERE DATE_SHIP < Reports!ParentReport!DATE_SHIP AND CUST_NUM =  Reports!ParentReport!CUST_NUM ORDER BY DATE_SHIP DESC;

[DanicaDear]

Between the time I read the first line and the last...boy were my wheels spinning! LOL.

[DanicaDear]

topher23,
I see you make a good point. I designed the database in this way because when I started I had *zero* DB experience, and I was reading a book that recommended it that way. I have never really thought about it until now. However, since I'm near completion, I'd rather not change it because I've built forms/queries/reports all over the place around the tables as they now exist. Thanks for the info because I have another DB to build after this one.

[DanicaDear]

I copied my query and replaced my SQL with NeoPa's suggestion. When I run the query, it does open, but with no records (I expect two.) When I go to design view, I get this error:
MS Access can't represent the join expression ts.DATE_SHIP>tS 2.DATE_SHIP in design view.
*One or more fields may have been deleted or renamed.
*The name of one or more fields or tables specified in the join expression may be misspelled.
*The join may use an operator that isn't supported in design view, such as > or <.

Clicking ok gets me the same error again. Clicking ok a second time opens the query in design view, where I see the new tables named tS and tS2, along with the join line and the correct field names.

When I put in NeoPa's SQL statement, I kept the first line from my SQL, which was the parameter definition. I have pasted it here just so I'm being as clear as possible.

PARAMETERS [Days Since Last Shipment] Short;
SELECT tS.ORDER_NUM AS NewShipment, tS.CUST_NUM AS Customer, tS.DATE_SHIP AS NewShipDate, tS.BOX_NUM AS NewRef, Max(tS2.ORDER_NUM) AS OldShipment, Max(tS2.DATE_SHIP) AS OldShipDate, Max(tS2.BOX_NUM) AS OldRef
FROM HOTSTICK_ORDERS AS tS INNER JOIN HOTSTICK_ORDERS AS tS2 ON tS.CUST_NUM = tS2.CUST_NUM
WHERE (((tS.DATE_SHIP)>=DateAdd('d',[Days Since Last Shipment],Date())))
GROUP BY tS.ORDER_NUM, tS.CUST_NUM, tS.DATE_SHIP, tS.BOX_NUM;

Also, why is it preferable to use a form in place of a parameter? I have figured out how to pass a value from a form to a query/report. But if it's just a single parameter, I think the parameters in Access 2007 are relatively easy to use. But I'm open to advice from the people who really know.