469,883 Members | 1,080 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,883 developers. It's quick & easy.

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

260 100+
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?
Nov 5 '09 #1

✓ answered by 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.

25 2551
MMcCarthy
14,534 Expert Mod 8TB
I think I understand what you are asking. Does the following make any sense to you.

tblShipment
Expand|Select|Wrap|Line Numbers
  1. ShipID (PK)
  2. CustID (FK)
  3. ShipDate
  4. RefNum
  5.  
Query
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.ShipID As NewShipment, T1.CustID As Customer, 
  2. T1.ShipDate As NewShipDate, T1.RefNum As NewRef, 
  3. T2.ShipID As OldShipment, T2.ShipDate As OldShipDate, 
  4. T2.RefNum As OldRef 
  5. FROM tblShipment As T1 INNER JOIN tblShipment As T2
  6. ON T1.CustID = T2.CustID
  7. AND Year(T2.ShipDate) = Year(T1.ShipDate) - 2
  8.  
Nov 5 '09 #2
DanicaDear
260 100+
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.
Nov 6 '09 #3
DanicaDear
260 100+
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?
Nov 6 '09 #4
MMcCarthy
14,534 Expert Mod 8TB
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
Nov 6 '09 #5
NeoPa
32,231 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tS.ShipID As NewShipment,
  2.          tS.CustID As Customer,
  3.          tS.ShipDate As NewShipDate,
  4.          tS.RefNum As NewRef,
  5.          Max(tS2.ShipID) As OldShipment,
  6.          Max(tS2.ShipDate) As OldShipDate,
  7.          Max(tS2.RefNum) As OldRef 
  8.  
  9. FROM     tblShipment As tS INNER JOIN
  10.          tblShipment As tS2
  11.   ON     (tS.CustID=tS2.CustID)
  12.  AND     (tS.ShipDate>tS2.ShipDate)
  13.  
  14. WHERE    tS.ShipDate>=DateAdd('d',60,Date())
  15.  
  16. GROUP BY tS.CustID,
  17.          tS.ShipID,
  18.          tS.ShipDate,
  19.          tS.RefNum
Nov 8 '09 #6
MMcCarthy
14,534 Expert Mod 8TB
@NeoPa
I agree with Ade here Danica as it's a much cleaner solution and mine I fear was a quick mockup :D

Mary
Nov 9 '09 #7
DanicaDear
260 100+
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.


Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Days Since Last Shipment] Short;
  2. 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
  3. 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
  4. WHERE (((Date()-[DATE_SHIP])>=[Days Since Last Shipment]) AND ((HOTSTICK_ORDERS.DATE_RET) Is Null))
  5. ORDER BY Date()-[DATE_SHIP] DESC;
  6.  
Now, I'm taking NeoPa's suggestion and replacing it with my real table/field names.
Expand|Select|Wrap|Line Numbers
  1. SELECT   tS.ORDER_NUM As NewShipment, 
  2.          tS.CUST_NUM As Customer, 
  3.          tS.DATE_SHIP As NewShipDate, 
  4.          tS.BOX_NUM As NewRef, 
  5.          Max(tS2.ORDER_NUM) As OldShipment, 
  6.          Max(tS2.DATE_SHIP) As OldShipDate, 
  7.          Max(tS2.BOX_NUM) As OldRef  
  8.  
  9. FROM     HOTSTICK_ORDERS As tS INNER JOIN 
  10.          HOTSTICK_ORDERS As tS2 
  11.   ON     (tS.CUST_NUM=tS2.CUST_NUM) 
  12.  AND     (tS.DATE_SHIP>tS2.DATE_SHIP) 
  13.  
  14. WHERE    tS.DATE_SHIP>=DateAdd('d',[Days Since Last Shipment],Date()) 
  15.  
  16. GROUP BY tS.CUST_NUM, 
  17.          tS.ORDER_NUM, 
  18.          tS.DATE_SHIP, 
  19.          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.
Nov 9 '09 #8
NeoPa
32,231 Expert Mod 16PB
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]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. StudentID       AutoNumber    PK
  3. Family          String        FK
  4. Name            String
  5. University      String        FK
  6. Mark            Numeric
  7. 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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 9 '09 #9
DanicaDear
260 100+
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.
Expand|Select|Wrap|Line Numbers
  1. Table HOTSTICK_ORDERS
  2. (This is where I seek to call up the info.)
  3. ORDER_NUM  Text        PK
  4. CUST_NUM   CUST_NUM
  5. DATE_SHIP  Date/Time
  6. BOX_NUM    Text
  7. DATE_RET   Date/Time
  8. REMARKS    Text
Expand|Select|Wrap|Line Numbers
  1. Table HOTSTICK_ORDER_DETAILS
  2. (This is the info I want to show if an order gets called up.)
  3. ORDER_NUM       Text    PK in this table. FK for above table
  4. QTY_SHIPPED     Number
  5. COMMODITY_DESC  Text    PK
  6. QTY_RETURNED    Number
  7. QTY_ABUSED      Number
  8. CHARGED_OUT     Yes/No
  9. REMARKS         Text
Nov 9 '09 #10
NeoPa
32,231 Expert Mod 16PB
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.
Nov 9 '09 #11
topher23
234 Expert 100+
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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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;
Nov 9 '09 #12
DanicaDear
260 100+
Between the time I read the first line and the last...boy were my wheels spinning! LOL.
Nov 9 '09 #13
DanicaDear
260 100+
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.
Nov 9 '09 #14
DanicaDear
260 100+
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.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Days Since Last Shipment] Short;
  2. 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
  3. FROM HOTSTICK_ORDERS AS tS INNER JOIN HOTSTICK_ORDERS AS tS2 ON tS.CUST_NUM = tS2.CUST_NUM
  4. WHERE (((tS.DATE_SHIP)>=DateAdd('d',[Days Since Last Shipment],Date())))
  5. GROUP BY tS.ORDER_NUM, tS.CUST_NUM, tS.DATE_SHIP, tS.BOX_NUM;
  6.  
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.
Nov 9 '09 #15
DanicaDear
260 100+
I have another idea. I don't quite know how to make it work...but it's an idea nonetheless.

Expand|Select|Wrap|Line Numbers
  1. if(DATE_RET=null and date()-DATE_SHIP (in days) >
  2. [Days Since Last Shipment] (that's the parameter),
  3. 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. ;-)
Nov 10 '09 #16
NeoPa
32,231 Expert Mod 16PB
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.
Nov 10 '09 #17
topher23
234 Expert 100+
@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.
Nov 10 '09 #18
DanicaDear
260 100+
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.
Nov 10 '09 #19
topher23
234 Expert 100+
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.
Nov 10 '09 #20
DanicaDear
260 100+
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!
Nov 10 '09 #21
DanicaDear
260 100+
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!
Nov 10 '09 #22
DanicaDear
260 100+
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!!!!!!!!!!!!!!!!!!!!!!!!!!
Nov 10 '09 #23
topher23
234 Expert 100+
Always glad to help!
Nov 10 '09 #24
DanicaDear
260 100+
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. :-)
Nov 10 '09 #25
NeoPa
32,231 Expert Mod 16PB
@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.
Nov 11 '09 #26

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
reply views Thread by RJN | last post: by
1 post views Thread by RJN | last post: by
1 post views Thread by z.ghulam | last post: by
2 posts views Thread by rinmanb70 | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.