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=consecutive 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]

@NeoPa
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]

Field           Type      IndexInfo
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.

@DanicaDear
This is helpful to see what fields you have available. Meta-data is better, but this gives us some good info anyway.
@DanicaDear
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.
@DanicaDear
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.
@DanicaDear
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.
@DanicaDear
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 HOTSTICK_ORDERS
(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 HOTSTICK_ORDER_DETAILS
(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>tS2.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.

[DanicaDear]

I have another idea. I don't quite know how to make it work...but it's an idea nonetheless.

if(DATE_RET=null and date()-DATE_SHIP (in days) >
[Days Since Last Shipment] (that's the parameter),
then display all orders with date()-DATE_SHIP<=2 (in years))

I'm still totally open to the SQL provided yesterday. It did look like we were so close to getting it to work, but I still find it responsible to put my own solutions on here if I think of them, even if they are elementary and possibly crappy. ;-)

[NeoPa]

Danica,

The reason I'm not replying immediately is I have a lot on and I think this will possibly take some quite convoluted SQL to work out. Hence not a 5-minute job.

While I'm visiting though, perhaps we can clear up a few points :

1. On line #4 of the HOTSTICK_ORDERS meta-data, in post #10, you have CUST_NUM CUST_NUM. Can you explain this. Or say what it should be.
2. @DanicaDear
   I would say that it's easier for the operator. They can see the value displayed on the form. There can be an associated label explaining exactly what it is and what it's for. It's a style call at the end of the day, so it's your choice. There's nothing fundamentally wrong with parameters in a design.
   ** Edit ** Having said that, take a look at Topher's answer in the post #18. It expresses the point very well.

[topher23]

@DanicaDear
I prefer to use forms rather than parameters because I don't have any control over the formatting of a parameter.

Say I want to pass dates to the query for a report. If I put them in my form, I can run code on the AfterUpdate event to make sure those dates are valid entries in the database. If I want all information for a particular customer in that date range, I can populate a combo box based on the dates to show only the customers that had orders during those dates. I can catch possible errors before they break the query.

By putting the parameter into a form, I can also make the form fields look like whatever I want, where a parameter in query will always pull up the same box. This allows for customization and helps create a specific feel for my application. When I'm designing an application for someone, I want it to say, "I'm all yours, I was designed solely for you," rather than, "HI! I'm MS Access!" If they don't think it was created in Access, I feel I've earned my paycheck.

Basically, it's all about having greater control over the presentation and formatting of the data being passed to the query.

[DanicaDear]

Good call, yes, it's CUST_NUM and text.

NeoPa, I appreciate you SO MUCH. I was thinking last night I should fly to London and take you to lunch. I wonder if my boss would go for that??

I am in no big rush to get this solution. I will gladly work around your terms! You have never left me hanging. Right now I am starting to bring data into my DB, so I have plenty to do. Tomorrow is a holiday in the US so if I got something working by next week I would be super happy.

[topher23]

By the way, you could still try this method:

@topher23
Set up your report to pull up the current order, then build a subreport with the SQL above as the Recordsource. Change ParentReport in the query to the report name and voila it ought to work.

[DanicaDear]

topher23,
I like your explanation. It makes perfect sense. I'm still consider myself new to this programming world but as I am able to do more sophistocated things, I can see where the form would be far better than the parameter. I am about to wrap up my first DB. I keep thinking of all the things I COULD do to it to make it better, but at some point I have to deliver a product and so I have to do what I can given my time constraints. When I am done with this DB I have to do another one for a glove lab. It is in this DB I will take all the things I've learned from Bytes and improve moving forward. Thanks again!

[DanicaDear]

topher23, I like your solution. I'm playing with it now. I will try to get it to work. May be Thursday before I can let you know.
Thanks!

[DanicaDear]

Ok, believe it or not, I GOT IT WORKING!!!!!!!! And yes, I'm shouting. LOL. It was so easy yet it took me longer than any other problem. Topher23, I didn't use your SQL but I used your idea and created my own stuff. I used the criteria in the query to show entries between 1 and 3 years old whose return date field was null. I put this in a subreport and like you said, Voila! How simple. I can see how this can be risky though, so I simply put a warning at the top of the report stating the limitations of the report, and stating should an order fall out of the 1-3 year range, data would need to be pulled up manually. For our needs, that would be extremely rare and so this will work great! Thanks to EVERYONE!!!!!!!!!!!!!!!!!!!!!!!!!!

[topher23]

Always glad to help!

[DanicaDear]

PS topher23,
I didn't use your SQL only because I couldn't get it working just quite right....and I'm very dangerous, so I found it safer to stay within my comfort zone, building the stuff as I have done in the past. :-)

[NeoPa]

@DanicaDear
That's not a bad thing. On the contrary, that's the ideal response. We are happiest when we help people to learn to do it themselves. Excellent.