UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Store Issue Voucher - Last Date Issued Of Each Items., Access 2007    
 
   
phil_andre
post Dec 17 2017, 12:03 PM
Post#1



Posts: 212
Joined: 7-December 16



Dear Support Team,

Greetings!

Here i am again, kindly download my demo for the new scenario required for the Store Issue Voucher regarding the last date issued on the items issuances..

Kindly check the design report. I am facing difficulty to make a query that could calculate automatically the last date.. My manager requested to see the last date issued on the items during each issuances.

Please advise accordingly.

thanks a lot in advance for the good team.!

Phil.
Attached File(s)
Attached File  Last_Issued.zip ( 48.05K )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Dec 17 2017, 12:11 PM
Post#2


UA Admin
Posts: 33,017
Joined: 20-June 02
From: Newcastle, WA


Good morning,

It might be useful to EXPLAIN the requirement first, rather than expect someone to analyze the report and interpret the situation for you.

Thanks.

--------------------
Go to the top of the page
 
RJD
post Dec 17 2017, 12:28 PM
Post#3


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


Hi Phil: You'll need a subquery to select the max issued date (NeededOutput?) and then inner join that to the other query by the Item and date to get what you want (if I understand your requirements correctly)...

SELECT qry_Transaction.SIVID, qry_Transaction.SIVDate, qry_Transaction.SIVDetails, qry_Transaction.ItemID, qry_Transaction.QtyOut, qry_Transaction.NeededOutput
FROM qry_Transaction INNER JOIN (SELECT ItemID, Max(neededOutput) AS MaxNO FROM qry_Transaction GROUP BY ItemID) AS MD ON (qry_Transaction.NeededOutput = MD.MaxNO) AND (qry_Transaction.ItemID = MD.ItemID);

Then use this as the record source for the report.

Take a look at my revision to your db and see if this is what you are attempting to do ...

HTH
Joe

Oh, and I agree with George, as I pretty much always do! (My name is Karma, and I believe in George) But you and I had worked together before and I thought I might have some understanding of what you are doing.
Attached File(s)
Attached File  Last_Issued_Rev1.zip ( 63.8K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 17 2017, 12:47 PM
Post#4



Posts: 212
Joined: 7-December 16



Thank you so much for the quick response , appreciated.

Actually this is what exactly report scenario i need.

You will see the image with last issue date in series...

not by maxing in one line...

this is the issuance details line by line. only need to see on the other column when it was last issued before of this SIVDate for each line.

But i need to run it thru query if possible just....

I dont want to put a code in itemid after update and give the maxdate result to last issued date field and i can do that method.

" this printout will be having a lot of items for approval, but before my boss will sign, he want to know this itemcode CAT0001 when it was last issued before that specific SIVDate each line.

thats why the report should like this.

SIVID SIVDate SIVDetails ItemID QtyOut this columnis the last date issued each detail. this is what i need.
1 01/12/2017 1 CAT0001 1 -
2 02/12/2017 2 CAT0001 5 01/12/2017
3 05/12/2017 3 CAT0001 10 02/12/2017
4 07/12/2017 4 CAT0001 15 05/12/2017

please do not confused. i assume this itemID/Code CAT0001 been repeated issued in this month...

so my boss will know that we make issuance for just a few days ago .... let assume CAT0001 is Hydraulic Motor. , i just demonstrating one itemcode or itemID.

on the report i just mentioned manually freetext the last issued column because i need like that in query or expression.
This post has been edited by phil_andre: Dec 17 2017, 12:57 PM
Attached File(s)
Attached File  Capture.JPG ( 50.45K )Number of downloads: 3
 
Go to the top of the page
 
phil_andre
post Dec 17 2017, 01:00 PM
Post#5



Posts: 212
Joined: 7-December 16



No Mr. Joe.

I know it's bit confusing.

Last issued column i just do it manually by freetext but its not automatic..

That is only a demo that i need the same result but in other way that can automatic , that gives the same result.

Below new image.
This post has been edited by phil_andre: Dec 17 2017, 01:09 PM
Attached File(s)
Attached File  Capture.JPG ( 59.5K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Dec 17 2017, 01:02 PM
Post#6


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


Sorry, Phil. But I am now lost on this. You showed a report output in your last post that you wanted, but that is what was already in the db you posted.

Please show us what the output should look like, using the data you provided, and what is different from what is there now to what you want.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 17 2017, 01:21 PM
Post#7



Posts: 212
Joined: 7-December 16



Mr. Joe,

Sory if you confused.

If you check the table design you will see once field name" needed output" and that particular field is just a demo ...

I need the same field that calculate automatically the last SIVDate for each transaction.

Example.

i issued on 5th Dec (50pcs) for itemcode CAT0001 - Hydraulic Filter , let say this is the first issue , so it will on the last date issue field as -

then on 8th Dec again issued the same itemcode CAT0001 - hydraulic filter then it will appear last issued date will be 5th Dec on that field.

then on 10th Dec again issued the same itemcode CAT0001 - hydraulic filter then it will appear the last issued date will be 8th Dec,.

and this last issued date line by line should be appear when am submitting the store issue vouchers for approval.

and my boss will know that from this date to this date there is a repeated issuance in short period so something wrong to that machine.
Go to the top of the page
 
phil_andre
post Dec 17 2017, 01:44 PM
Post#8



Posts: 212
Joined: 7-December 16



Mr. Joe,

Please find attached phil_lastissued.accdb and kindly check now the unbound object to avoid confusion.

below result i need,, i just do it in excel.

i change the term from Last Issued Date to Last SIV Date for better understanding.
This post has been edited by phil_andre: Dec 17 2017, 01:45 PM
Attached File(s)
Attached File  Phil_Last_Issued.zip ( 53.72K )Number of downloads: 1
Attached File  EXCEL.JPG ( 29.96K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Dec 17 2017, 02:20 PM
Post#9


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


Ah, okay, I think we had a language issue. To me, you are not looking for the Last Date at all, but the IMMEDIATELY PREVIOUS DATE for an Item. To do this you can use a DMax function to find that date for the indicated Item.

CODE
SELECT qry_Transaction.SIVID, qry_Transaction.SIVDate, qry_Transaction.SIVDetails, qry_Transaction.ItemID, qry_Transaction.QtyOut,
DMax("[SIVDate]","[qry_Transaction]","[ItemID]=" & [ItemID] & " And Format([SIVDate],'yyyymmdd') <'" & Format([SIVDate],'yyyymmdd') & "'") AS PreviousSIVDate
FROM qry_Transaction;

Then you can use this query as the record source for the report. See if this is what you are trying to do.

Note two things:

1) Since you are using European date formats, you must adjust the date comparison to a common format. I am using one of the methods.

2) In tbl_SIVDetails you are making ItemID a Lookup field. This is a VERY bad idea. See HERE for a discussion of Lookup fields in tables. Some may disagree that Lookup fields in tables are evil, but I personally support that view. I recommend you get rid of the lookup field and just use a numeric field. Then you can link to the description field when you need it.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Rev1.zip ( 25.41K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 17 2017, 02:38 PM
Post#10



Posts: 212
Joined: 7-December 16



Dear Mr. JOE,

Well tnx a lot and i will check this out after bed. Its quite silent now here at abu dhabi 11.36pm.
Go to the top of the page
 
RJD
post Dec 17 2017, 02:43 PM
Post#11


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


You are welcome. Let us know if this meets your requirements.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 17 2017, 10:24 PM
Post#12



Posts: 212
Joined: 7-December 16



Good Morning Mr. Joe,

Perfect, this is what i need exactly.


Thank you very very much to all.
Go to the top of the page
 
RJD
post Dec 17 2017, 11:29 PM
Post#13


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that works for you. thumbup.gif We are all happy to assist.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
HairyBob
post Dec 18 2017, 01:18 AM
Post#14



Posts: 987
Joined: 26-March 08
From: London, UK


Hi Phil,

Just to let you know that I saw the personal message you sent me at 7:47pm yesterday entitled "Help". I see that Joe has yet again helped you to sort your problem out. Sorry I didn't get to it in time.

Hairy thumbup.gif
This post has been edited by HairyBob: Dec 18 2017, 01:18 AM
Go to the top of the page
 
phil_andre
post Dec 18 2017, 11:54 AM
Post#15



Posts: 212
Joined: 7-December 16



Dear Mr. Joe,

Greetings!

I have small issue if you can advise to fixed this. here is the screenshot.

how to avoid pop up error msg and avoid display #error if the line detail without itemID but that line has description used for comments / remarks only.

i want to hide that #Error in text field. or make it blank if no itemID exist.

thanks .
This post has been edited by phil_andre: Dec 18 2017, 11:59 AM
Attached File(s)
Attached File  Error_Msg.JPG ( 20.92K )Number of downloads: 3
Attached File  Error_from_Lines_without_ID.JPG ( 12.91K )Number of downloads: 0
 
Go to the top of the page
 
phil_andre
post Dec 18 2017, 12:14 PM
Post#16



Posts: 212
Joined: 7-December 16



Mr. Joe,

Please find attached rev02 ACCDB with #Error, please help how to make it blank and avoid pop msge if no itemID.

i have the same scenario.


Attached File(s)
Attached File  Phil_Last_Issued_Rev2.zip ( 48.79K )Number of downloads: 1
Attached File  Capture.JPG ( 67.87K )Number of downloads: 1
 
Go to the top of the page
 
phil_andre
post Dec 18 2017, 12:42 PM
Post#17



Posts: 212
Joined: 7-December 16



Please ignore Mr. Joe. Its okay now as per below sample.

=IIf(IsError([txtHiddenTextBoxName]),"",[txtHiddenTextBoxName])

thanks again.
Go to the top of the page
 
RJD
post Dec 18 2017, 12:52 PM
Post#18


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


Hi Phil: Rather than "cover up" the error in the last column (which, of course, gives you the display without the error indication), my preference is to eliminate the error completely. See the revised query below ...

SELECT qry_Transaction.SIVID, qry_Transaction.SIVDate, qry_Transaction.SIVDetails, qry_Transaction.ItemID, qry_Transaction.Descriptioin, qry_Transaction.QtyOut,
DMax("[SIVDate]","[qry_Transaction]","[ItemID]=" & NZ([ItemID],0) & " And Format([SIVDate],'yyyymmdd') <'" & Format([SIVDate],'yyyymmdd') & "'") AS PreviousSIVDate
FROM qry_Transaction;

There may be other uses for the query results, so I think it may be better to deal with the situation at the source rather than fix the display later.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Rev3.zip ( 27.47K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 18 2017, 01:58 PM
Post#19



Posts: 212
Joined: 7-December 16



Excellent Mr. JOE.

Thanks a lot.
Go to the top of the page
 
RJD
post Dec 18 2017, 02:05 PM
Post#20


UtterAccess VIP
Posts: 8,470
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Phil. Continued success with your project ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    17th July 2018 - 02:25 PM