question

Runner-2178 avatar image
0 Votes"
Runner-2178 asked thedbguy answered

MS access speed problem

Hello ,
I have a problem with a speed of more than 1000 transactions will slow down the program very much, the query is displayed relatively quickly, but converting to a table takes a long time, how can I quickly transfer a query to a table, I tried everything but the program is very slow. I do not understand how it shows quickly, but it cannot create with such speed.

I see the query screen, but I cannot copy what I see, it starts calculating and it takes a long time, can I copy the screen like a screen without secondary calculating.

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. Not sure what's going on in the background that makes the process of transferring the query data into a table. So, the question then becomes, why do you want to create that table? If we knew your reason, we might be able to suggest another way of getting to the data you need. Just a thought...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

Hello I have speed problem access cant work more then 1000 transaction, but if i convert xp2 to table it will work milion transaction without problem
i have xp table
i have query one named xp1
SELECT xp.ID, xp.Date, Format $ ([xp.Date], "yyyymmdd") & Format ([xp.ID], "00") AS ID2, xp.typeID, xp.Goods, xp.quantity, xp.Cost, xp.Cost / xp.quantity AS unitcost, IIf ([typeID] = "IN", [quantity], - 1 [quantity]) AS rtquantity, xp.Company, xp.Cost / xp.quantity AS unitPrice, xp. [1PaymentDate], xp. [1PaymentAmount], xp. [2PaymentDate], xp. [2PaymentAmount], xp. [3PaymentDate], xp. [3PaymentAmount], IIf ([typeID] = "IN", - 1 [Cost] + [1PaymentAmount] + [2PaymentAmount] + [3PaymentAmount], IIf ([typeID] = "OUT", [Cost] - [1PaymentAmount] - [2PaymentAmount] - [3PaymentAmount], 0)) AS rtblanace, IIf ([typeID] = "IN", - 1 * [Cost], IIf ([typeID] = "OUT", [Cost], 0)) AS rtpl
FROM xp;

and i have query two named xp 2

SELECT xp1.ID, xp1.Date, xp1.ID2, xp1.typeID, xp1.Goods, xp1.quantity, xp1.Cost, xp1.unitcost, Val(DSum("[xp1].rtquantity","[xp1]","Goods = '" & [Goods] & "' " & "AND ID2 <= " & [ID2])) AS ∑quantity, xp1.Company, xp1.unitPrice, xp1.[1PaymentDate], xp1.[1PaymentAmount], xp1.[2PaymentDate], xp1.[2PaymentAmount], xp1.[3PaymentDate], xp1.[3PaymentAmount], Val(DSum("[xp1].rtblanace","[xp1]","Company = '" & [Company] & "' " & "AND ID2 <= " & [ID2])) AS ∑blanace, Val(DSum("[xp1].rtpl","[xp1]","Goods = '" & [Goods] & "' " & "AND ID2 <= " & [ID2])) AS ∑ProfitAndLossByGoods, Val(DSum("[xp1].rtpl","[xp1]","ID2<=" & [ID2])) AS ∑ProfitAndLoss
FROM xp1;


i want create xp0 table from xp2 query but it works very slow i dont know what to do.
i need speed nathing more, and i know only table cant work so fast
please help

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered Runner-2178 edited

The most interesting access shows the calculation fast but copying or converting to table is a very slow process. how i can copy the given data without recalculation, it's like in Excel all formulas are removed and copied as text.
and if I copy the last recordsшт in xp2, the program does not start recalculation, but if I copy everything, the terrible recalculation process begins.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. Just curious, if you export the query to Excel, is it slow too?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

yes very slow i can send file for you

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

no i cant send access file

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

if you give me email i send access file

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

No promises but here it is: thedbguy (at) gmail (dot) com

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

thank you i send file

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. What is too slow for you? I was able to create a table in about a minute.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.