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



Posts: 212
Joined: 7-December 16



Good Evening to you Mr. Joe.

I was sleep already last night and today major busy at work.

by the way, kindly download my database with data, i just uploaded it thru my cloud storage because of size limitation.

<removed>

it contains 68k lines , i took some details from my official apps, and i have added a range form.

kindly try from MP06000 to MP06000 and you will see it became stroke.

Please check and advise the query...

my main problem here when running the query speed, very2x slow, i am just running query for one voucher, if i run from MP06000 to MP06010 then it is freezing and my mind became blown out.

thanks again for the query script.

appreciate always your very kind assistance, guidelines and support.
This post has been edited by ghubbell: Dec 21 2017, 11:41 AM
Go to the top of the page
 
RJD
post Dec 21 2017, 11:08 AM
Post#42


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


Phil: I don't think we allow the reference you posted. I have asked an Admin to look at this and decide.

In any case, I will not download from there. Please attach a zipped file as is usual here.

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 21 2017, 11:13 AM
Post#43



Posts: 212
Joined: 7-December 16



Oh apology. the zip file is 3.7MB after compress. original ACCDB size is 13MB.

i will try to split it up into to archive.

will add also AssetCode in qry_SIVBatchRangePriceLastSIV.
Go to the top of the page
 
phil_andre
post Dec 21 2017, 11:18 AM
Post#44



Posts: 212
Joined: 7-December 16



Mr. Joe,

Kindly try attached.. highly compressed. thanks.
Attached File(s)
Attached File  Phil_DataTest.zip ( 1.9MB )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Dec 21 2017, 11:44 AM
Post#45


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


Hi Phil: It looks like you did not post the latest query approach without domain functions (the one with the query I broke apart for you). Have you tried the approach without the domain functions?

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 21 2017, 11:49 AM
Post#46



Posts: 212
Joined: 7-December 16



Hi Joe,

I did not try that domain function.

it seems my attachment is corrupt.

I have to re-upload it.
Go to the top of the page
 
RJD
post Dec 21 2017, 11:52 AM
Post#47


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


Actually, WITHOUT the domain function, like my last attachment, with the query broken apart like you requested.

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 21 2017, 12:07 PM
Post#48



Posts: 212
Joined: 7-December 16



i am splitting the database now,, otherwise i cannot reach below 3MB.
Go to the top of the page
 
RJD
post Dec 21 2017, 12:11 PM
Post#49


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


Have you tried the latest query? The one(s) without the domain functions?

You should do that first before you post another version of your db.

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 21 2017, 12:13 PM
Post#50



Posts: 212
Joined: 7-December 16



Mr. Joe.

Here is the 3 splitted parts.

please extract all. thanks.

RE-UPLOADED 3 PARTS

First extract Phil_DataTest01 & Phil_DataTest02 then Phil_DataTest

thanks.
This post has been edited by phil_andre: Dec 21 2017, 12:20 PM
Attached File(s)
Attached File  Phil_DataTest01.zip ( 1.47MB )Number of downloads: 1
Attached File  Phil_DataTest02.zip ( 1.47MB )Number of downloads: 1
Attached File  Phil_DataTest.zip ( 784.89K )Number of downloads: 1
 
Go to the top of the page
 
phil_andre
post Dec 21 2017, 01:24 PM
Post#51



Posts: 212
Joined: 7-December 16



Hi Joe, so far i stuck on this stage and may i ask you on the attached above for the Phil_DataTest, that is my actual footage for the last SIV, on how it works.

and do you mean the latest query you uploaded without domain which you re=-constructed.

i downloaded that last night and i was sleep bcoz almost midnight. i have to try it now.

Go to the top of the page
 
RJD
post Dec 21 2017, 01:27 PM
Post#52


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


Let us know how the query without the domain function works. Then we can look further if needed.

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 21 2017, 01:36 PM
Post#53



Posts: 212
Joined: 7-December 16



Hello Joe, im working my actual database backup now without domain function and hope i will success without conflicts.

Although, i am attaching above my actual data from some parts of my database for your kind review and advise.

Go to the top of the page
 
phil_andre
post Dec 21 2017, 02:08 PM
Post#54



Posts: 212
Joined: 7-December 16



Help Joe,

please see attached both version of you and me.

it is not matching the qry_D result when i am trying to copy you query to my access 2007 version.

or did i missed something or did wrong... or that special function you use not being visible.


Attached File(s)
Attached File  Phil_and_Joe_Version.zip ( 82.11K )Number of downloads: 1
 
Go to the top of the page
 
phil_andre
post Dec 21 2017, 02:17 PM
Post#55



Posts: 212
Joined: 7-December 16



Joe Version:

SELECT qry_Transaction.SIVDate, qry_Transaction.ItemID, qry_Transaction.Allocation, qry_Transaction.QtyOut, Max(qry_Transaction_1.SIVDate) AS PreviousDate
FROM qry_Transaction LEFT JOIN qry_Transaction AS qry_Transaction_1 ON (qry_Transaction.SIVDate>qry_Transaction_1.SIVDate) AND (qry_Transaction.Allocation=qry_Transaction_1.Allocation) AND (qry_Transaction.ItemID=qry_Transaction_1.ItemID)
GROUP BY qry_Transaction.SIVDate, qry_Transaction.ItemID, qry_Transaction.Allocation, qry_Transaction.QtyOut, qry_Transaction_1.SIVDate;



Phil Version:

SELECT qry_Transaction.SIVDate, qry_Transaction.ItemID, qry_Transaction.Allocation, qry_Transaction.QtyOut, Max(qry_Transaction_1.SIVDate) AS PreviousDate, qry_Transaction_1.SIVDate
FROM qry_Transaction LEFT JOIN qry_Transaction AS qry_Transaction_1 ON (qry_Transaction.Allocation = qry_Transaction_1.Allocation) AND (qry_Transaction.ItemID = qry_Transaction_1.ItemID)
GROUP BY qry_Transaction.SIVDate, qry_Transaction.ItemID, qry_Transaction.Allocation, qry_Transaction.QtyOut, qry_Transaction_1.SIVDate;

Attached File(s)
Attached File  Attemp01_Joe_Version.JPG ( 27.96K )Number of downloads: 2
Attached File  Attemp02_Joe_Version.JPG ( 32.17K )Number of downloads: 1
Attached File  Joe_Version_Design_View.JPG ( 71.74K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Dec 21 2017, 02:38 PM
Post#56


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


Yes. You cannot represent the query in Design View because of the type of join. Do not look at it in Design View. Look at in SQL View only.

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 21 2017, 03:56 PM
Post#57



Posts: 212
Joined: 7-December 16



Hi Joe,

Finally, works perfectly and very smooth on the 2nd option without domain functions, it loads faster even have hundred thousands of lines.

Everything is okay now. and i am going to compile with this tomorrow.

thanks a lot... it's almost a week to solve this problem.

Joe Version:

SELECT qry_Transaction.SIVDate, qry_Transaction.ItemID, qry_Transaction.Allocation, qry_Transaction.QtyOut, Max(qry_Transaction_1.SIVDate) AS PreviousDate
FROM qry_Transaction LEFT JOIN qry_Transaction AS qry_Transaction_1 ON (qry_Transaction.SIVDate>qry_Transaction_1.SIVDate) AND (qry_Transaction.Allocation=qry_Transaction_1.Allocation) AND (qry_Transaction.ItemID=qry_Transaction_1.ItemID)
GROUP BY qry_Transaction.SIVDate, qry_Transaction.ItemID, qry_Transaction.Allocation, qry_Transaction.QtyOut, qry_Transaction_1.SIVDate;
This post has been edited by phil_andre: Dec 21 2017, 04:05 PM
Go to the top of the page
 
RJD
post Dec 21 2017, 06:53 PM
Post#58


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


You are very welcome. Glad that works for you. thumbup.gif

The query you posted looks like the first in the three-step process that you wanted broken apart. Did you run the last query to get the results with the previous quantity as well?

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 21 2017, 09:32 PM
Post#59



Posts: 212
Joined: 7-December 16



Good morning Mr. Joe.

Yes, i run the three query from broken apart as per your advise.

I am only getting difficulty on the first query but i understand now thru SQL view.

The second and third query i have no problem after first qry success

But honestly, the three query you broke apart is very helpful and easy to understand.

And you are absolutely right that we need to test the last query before proceeding further.

Already made the 3 queries with my required result.

Very impressive, the query so fast no matter what the data size is it.

Thanks a lot Joe, you did a very good job and good advise.

Have a nice day.
This post has been edited by phil_andre: Dec 21 2017, 09:48 PM
Go to the top of the page
 
RJD
post Dec 21 2017, 10:42 PM
Post#60


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


thumbup.gif

--------------------
"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    19th July 2018 - 06:17 PM