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 19 2017, 01:32 AM
Post#21



Posts: 196
Joined: 7-December 16



Dear Mr. Joe,

Lastly, need your support please. Also i attached the revised4 ACCDB added with allocation for the machine tag.

the LastSIVDate mapping should be from itemID + Allocation (means this two condition need for SIVLastDate output) and not only fro the itemID.

Moreover,

Please advise the SQL for the allocation field if datatype is text and datatype is number.


Thanks a lot in advance.
Attached File(s)
Attached File  Phil_Last_Issued_Rev4.zip ( 40.98K )Number of downloads: 1
Attached File  PHIL_EXCEL.JPG ( 38.01K )Number of downloads: 1
 
Go to the top of the page
 
phil_andre
post Dec 19 2017, 05:21 AM
Post#22



Posts: 196
Joined: 7-December 16



Mr. Joe,

Please find attached ACCDB, i tried to club it by aliasing the itemID + allocation but not success.
Go to the top of the page
 
phil_andre
post Dec 19 2017, 05:51 AM
Post#23



Posts: 196
Joined: 7-December 16



Dear Mr. Joe,

Here is my final demo for the LastSIVDate for clubbing itemID+Allocation.

First demo for Allocation with number datatype. (I success , i have no problem with numerical)

Second demo for Allocation with text datatype. (Not sucess, it gives error field, but also need this scenario)


please advise how to fixed the error for text datatype for allocation.

thanks.


Attached File(s)
Attached File  Allocation_Number_and_Text_Type_Need_Both_Scenario.zip ( 105.55K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Dec 19 2017, 08:59 AM
Post#24


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Hi again, Phil: The source of your problem is that you are using a Lookup field in a table - ItemID in tbl_SIVDetails. So the value in the field appears to be text but is actually numeric. (See HERE for discussion of Lookup fields.) I suggest you convert this to simply a numeric type with no Lookup and then use a form to do entry with a combobox.

That said, you can adjust your SQL to accommodate the numeric by converting it to text using a Format function. Then when you concatenate it with [Allocation] use a + instead of a & in the comparison. This will Null the whole expression if the [Allocation] is Null, leading to a default of "X" which will mean no match and a Null result in this case. Which is what you want. You also need to add the single quotes around the comparison to indicate that it is text.

DMax("[SIVDate]","[qry_Transaction]","Format([ItemID],'0000') & [Allocation]='" & Nz(Format([ItemID],'0000') + [Allocation],'X') & "' And Format([SIVDate],'yyyymmdd') <'" & Format([SIVDate],'yyyymmdd') & "'") AS PreviousSIVDate

See if this is what you need.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Allocation_Text_Type_Rev1.zip ( 27.63K )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 19 2017, 10:34 AM
Post#25



Posts: 196
Joined: 7-December 16



Noted with thanks Mr. Joe.

Yes, this is what exactly i need.

Thanks a lot and the guidelines.


Mr. Joe, what if, if i want to add the last quantity issued like for CRANE01 the previous SIVDate will be 01/12/2017 (1) , means (1) is the last quantity issued to that machine.

Just i prefer that result to add the quantity in close parethesis.

Just if you have time.

thank you and have a nice day.
This post has been edited by phil_andre: Dec 19 2017, 10:54 AM
Go to the top of the page
 
phil_andre
post Dec 19 2017, 11:58 AM
Post#26



Posts: 196
Joined: 7-December 16



Mr. Joe,

Can it be possible like this. attached image. PreviousSIVDate & QtyOut

I keep trying to add the quantity but am not success.

As of now my generated report only for PreviousSIVDate.

Appreciate much your usual support.

thanks.
Attached File(s)
Attached File  PreviousSIVDate___QtyOut.JPG ( 41.71K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Dec 19 2017, 02:57 PM
Post#27


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Hi again Phil: See the new revision to your db. One way to do this is to add the qty by using a DLookup using the ItemID, Allocation and Previous Date. It is not necessary to concatenate the date and qty fields in the query. Users should never be looking at the query results directly, but at forms and reports, where you can display the results as you wish.

And, once again, you really need to do something about changing the ItemID Lookup field in the tbl_SIVDetails table. This will continue to give you problems.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Allocation_Text_Type_Rev2.zip ( 34.55K )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 19 2017, 03:22 PM
Post#28



Posts: 196
Joined: 7-December 16



Mr. Joe,

thanks a lot, i downloaded the file but no result display in prev qtyout.


By the way, i have a problem when running the query for itemID + allocation it takes a lot of time to load.

i have around 58k transaction line details.

but if i only take the itemID in SIVLastDate then it loads quickly and while itemID+allocation it takes a lot of time to read for the result.

i tried both number and text datatype very slow when combining the two field condition.

how to fixed that. thanks.

I want to add 50k lines in this demo so that u will know how slow is it when running itemid +allocation.
This post has been edited by phil_andre: Dec 19 2017, 03:33 PM
Attached File(s)
Attached File  Capture.JPG ( 63.73K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Dec 19 2017, 03:49 PM
Post#29


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Yes, the last iteration did not deal with the difference in US and European dates. See the attached revision that does.

And, yes, this will be slow with the domain function approach with that many records. You hadn't said before what size table you were dealing with. There are other methods that may be faster, but that will take some time to develop. I do not have time today to devote to this, so you should study what I have done and research other ways to approach this, such as using subqueries and/or joins instead of domain functions.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Allocation_Text_Type_Rev3.zip ( 28.07K )Number of downloads: 4
 

--------------------
"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
 
RJD
post Dec 19 2017, 08:26 PM
Post#30


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


I had a few minutes, so I took another look at your issue with response time ... and reworked the queries so they would not use domain functions. This should make it run faster. I also condensed all the queries into a single query with sub- and sub-sub-queries.

I also had issues with your design. Perhaps this is because I do not understand what all you are trying to do. I eliminated one table and put the dates directly into the details. And I changed the Lookup field to make it a simple numeric field. See if this makes sense with what you are trying to do.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Allocation_Text_Type_Rev4.zip ( 25.95K )Number of downloads: 4
 

--------------------
"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 19 2017, 10:43 PM
Post#31



Posts: 196
Joined: 7-December 16



Very Good Morning to you Mr. Joe,

First of all i would like to thanks very much to all for all the support and this effort. This scenario is another challenge to me and this site really helpful.

For query result , yes this is what i need , later i will try to apply this on my massive query details and i hope it wud be much faster than before.

I'm at office now and I'll come back to you Mr. George for the query performance.
Go to the top of the page
 
RJD
post Dec 19 2017, 11:30 PM
Post#32


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Be sure to let us know if the query response is satisfactory. Good luck with your testing.

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 20 2017, 12:16 AM
Post#33



Posts: 196
Joined: 7-December 16



Mr. Joe, could you please do me a favor to show me the design mode on how you created the Q table in SQL mode.

I seen there is a max function in SQL. But i would rather to see that on design mode without using Q alias for my better understanding.

thanks.

Go to the top of the page
 
RJD
post Dec 20 2017, 12:31 AM
Post#34


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Actually, you will not be able to see the query correctly in Design View, since it has three levels of queries. To see this properly in Design View, the query will have to be de-constructed into three saved queries with two queries relying on another saved query.

This will take some time to do. And that should be a good exercise for you to try. Just pick out the Q query and save it in another query and then you can see that one in Design View.

Right now it is almost midnight here and I am shutting down for the night.

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 20 2017, 12:57 AM
Post#35



Posts: 196
Joined: 7-December 16



Mr. Joe, sorry about that. Yes indeed it will take time, it needs some queries to save.. I will keep trying... but i like the way you design in SQL, i will keep trying. thanks and good night to you. here is early morning.
This post has been edited by phil_andre: Dec 20 2017, 12:58 AM
Go to the top of the page
 
phil_andre
post Dec 20 2017, 10:47 AM
Post#36



Posts: 196
Joined: 7-December 16



Good Evening Joe.

Am still figuring out and i am facing difficulty , spending time just to break the Q alias table/qry you made to make it saved query instead. I know it is easy to think but my brain became drain and stress on keep trying to breakthrough that Q. It is quite difficult for me.

Otherwise, i will moved to rev3 method instead of rev4., the problem in rev3 is that when you combine two field thru alias it will take time to load even you have less than 20 lines. I will update the demo table

But i want to try this rev4.accdb
Attached File(s)
Attached File  Saved_Query_for_Q_alias.zip ( 39.61K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Dec 20 2017, 11:38 AM
Post#37


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Hi Phil: It is not necessary for you to split out the query parts for you to use it. You should be able to use it as it is (with subqueries), given that you fix your tables as I discussed, or as an alternative, change the query to look at the tables as you had them originally.

But I will take a look at this later and see about de-constructing the query.

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 20 2017, 12:11 PM
Post#38



Posts: 196
Joined: 7-December 16



Ok Joe, hopefully soon to convert that to saved query.

by the way, am going to upload database now with 48k lines to compare the speed in single condition and double condition.

this is the true test with huge data.

i reconstruct the previous demo now with 48k lines my appsource... i took some pieces..
Go to the top of the page
 
RJD
post Dec 20 2017, 12:25 PM
Post#39


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


Phil: I de-constructed the query into three saved queries. The end result query is qry_Main. This is fed by qry_Q, which is fed by qry_D.

I also went back to the tables that you had to begin with. This means that the Lookup field is still there and the dates are still in the separate table. So I went back to your query qry_Transaction. I STRONGLY suggest you fix these design problems, as I have indicated before. And you really need to develop your skills in this area if you want to continue making databases like this.

HTH
Joe
Attached File(s)
Attached File  Phil_Last_Issued_Allocation_Text_Type_Rev6.zip ( 32.85K )Number of downloads: 2
 

--------------------
"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 20 2017, 12:58 PM
Post#40



Posts: 196
Joined: 7-December 16



thanks a lot Joe. let me try this.
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    19th January 2018 - 02:40 AM