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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Query One Record At A Time., Access 2016    
 
   
JAchord
post Mar 31 2020, 12:29 PM
Post#1



Posts: 227
Joined: 11-July 14



Is there any way to run a query record by record instead of all matching records at once. I am inserting all the values into a temp table. I would like it to find the top value then insert it into the temp table and run a query based on that record and then insert that record into the temp table. The goal is to keep looping this until the records are copied. After all the sub records are copied for that record it would rerun the first query to get the next record in the list and run the sub-queries for that.

Why would I want to do this you ask. Well I can get all of the data I need using the queries I have but it does not put it in any order. So I need it to step through the queries so I can put a counter on each record to set up a sequence for future data manipulation.
Go to the top of the page
 
theDBguy
post Mar 31 2020, 12:44 PM
Post#2


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you can specify the order of the records, then you can query all of them and add the sequential numbers all at once. Try searching for "ranking records."

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JAchord
post Mar 31 2020, 03:36 PM
Post#3



Posts: 227
Joined: 11-July 14



That's the problem. I am using historical Data that has a sequence field but does not have data in all the fields. Basically I am building a tree. First I have to pull records from one table then I have to pull records from another table. The first table has the top-level item then the second has lower-level items. Sometimes it stops at the second level sometimes it keeps querying based on the lower level records. Unfortunately, I can not find how they populated the temp table order in the original DB. It's not Access.

I really only need to get it to work one time then I could just populate the sequence field and use that to get the correct order from there on.
Go to the top of the page
 
MadPiet
post Mar 31 2020, 04:04 PM
Post#4



Posts: 3,703
Joined: 27-February 09



What is the original database? Can you write queries against it? (You can write pass-through's in Access that just "forward" the query text to the back end database, so you could pass, say, a T-SQL (SQL Server's version of SQL) statement to a SQL Server backend database, and it would run the query you passed to it and return the results to Access.
This post has been edited by MadPiet: Mar 31 2020, 05:03 PM
Go to the top of the page
 
JAchord
post Mar 31 2020, 04:58 PM
Post#5



Posts: 227
Joined: 11-July 14



I was trying .findfirst to work but I don't think that this method is meant to be used like that. Or at least I could not figure out how to get it to go to the next record.

https://docs.microsoft.com/en-us/office/cli...irst-method-dao
Go to the top of the page
 
MadPiet
post Mar 31 2020, 05:10 PM
Post#6



Posts: 3,703
Joined: 27-February 09



Although some here may disagree, I'd stay away from recordsets unless there's no other way to accomplish what you're trying to do. Using CROSS APPLY in SQL Server with TOP VALUES is the easiest way I know of doing what you want, but I don't know what your backend database is. If your backend is SQL Server 2012 or later, you can do something like this:

CODE
USE AdventureWorks2017;
GO

--- return last 3 sales orders per customer
SELECT c.CustomerID
    , c.AccountNumber
    , ca.OrderDate
    , ca.SalesOrderID
    , ca.SalesOrderNumber
FROM Sales.Customer c
CROSS APPLY (
            SELECT TOP 3 soh.SalesOrderID
                ,  soh.SalesOrderNumber
                ,  soh.OrderDate
                ,  soh.CustomerID
            FROM Sales.SalesOrderHeader soh
            WHERE soh.CustomerID = c.CustomerID
            ORDER BY soh.OrderDate DESC
            ) ca
ORDER BY c.CustomerID ASC;

This post has been edited by MadPiet: Mar 31 2020, 05:33 PM
Go to the top of the page
 
FrankRuperto
post Mar 31 2020, 06:01 PM
Post#7



Posts: 968
Joined: 21-September 14
From: Tampa, Florida USA


MadPiet,

PT queries are not going to work on a Progress db. Why not just import the data and sort it in an Access temp table or use a Dynaset?
This post has been edited by FrankRuperto: Mar 31 2020, 06:04 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Mar 31 2020, 06:19 PM
Post#8



Posts: 3,703
Joined: 27-February 09



Does ProgessDB support LATERAL JOIN?
https://medium.com/kkempin/postgresqls-late...in-bfd6bd0199df

Can't find documentation on ProgressDB anywhere!
Go to the top of the page
 
FrankRuperto
post Mar 31 2020, 06:47 PM
Post#9



Posts: 968
Joined: 21-September 14
From: Tampa, Florida USA


Forgot to mention Progress was renamed to OpenEdge, but its essentially the same product. There's an ODBC driver for it, but not sure if its able to translate T-SQL with LATERAL JOINS. Progress/OpenEdge uses a proprietary 4GL called ABL(Advanced Business Language). It's equivalent SQL SELECT statement syntax is FOR EACH ...

https://www.progress.com/odbc/openedge

UPDATE: I didn't find any support for LATERAL JOINS, so don't know how ODBC would translate that in a Progress query:

https://media.datadirect.com/download/docs/...ml%23wwID0EHQ36

But again, why jump through all these hoops when you can just import the data into Access and manipulate it there?
This post has been edited by FrankRuperto: Mar 31 2020, 06:58 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
Jeff B.
post Mar 31 2020, 07:50 PM
Post#10


UtterAccess VIP
Posts: 10,479
Joined: 30-April 10
From: Pacific NorthWet


<JAchord>

As I recall, the .findfirst method in Access finds the first record … based on how Access chooses to store data. If you have a way to sequence/order your records, you can use a query to get "the next" record.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
JAchord
post Apr 1 2020, 09:26 AM
Post#11



Posts: 227
Joined: 11-July 14



I am already out of progress and in Access. I finally figured out how the records are related. What I could not find is how they structured the data when it was placed in a temp table prior to export or for further manipulation. The best I can tell from some "find first" statements and loops is that it is done one record at a time.
Go to the top of the page
 
FrankRuperto
post Apr 1 2020, 01:16 PM
Post#12



Posts: 968
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (JAchord)
how they structured the data when it was placed in a temp table prior to export

Wouldn't the field names give you an indication? The export is probably a mixture of fields from different related tables, same as you would expect to see in a SQL VIEW flat file that gets exported to Excel.

As to your 1.8gig accdb, have you moved the 5.3M notes records to a new accdb and relinked to that table?
This post has been edited by FrankRuperto: Apr 1 2020, 01:16 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Apr 1 2020, 04:41 PM
Post#13



Posts: 227
Joined: 11-July 14



I have not moved the notes yet trying to get this other issue worked out first. It is the last thing I need to accomplish to all but eliminate using the old system.

Here is the code I use to pull all the data maybe it will give someone an idea that I am missing. Ideally, it would put a number in a field as it populates to sort by. I guess that is not an option.

CODE
Public Function IndentedBOM()

Dim tjob As String
Dim tsuffix As Integer
Dim titem As Variant
Dim nParentQty As Integer
Dim nSF As Integer
Dim cMode As Variant
Dim strSQL As String

MainItem:

   TempVars!tlevel = 0
If TempVars!tlevel = 0 Then
    strSQL = "INSERT INTO tbomtbl ( xlevel, imitem, jbtype, jbjob, jbsuff, TJobIDKEY ) " & vbCrLf & _
        "SELECT 0 AS [Level], Job.item, Job.type, Job.job, Job.suffix, Job.JobIDKey " & vbCrLf & _
        "FROM Job " & vbCrLf & _
        "WHERE (((Job.JobIDKey)=[Forms]![Itemfrm]![JobCBX]));"
    
    DoCmd.RunSQL strSQL
            
End If
    
Nextlevel:

    'Do
    TempVars!PrevLevel = TempVars!tlevel.Value
    TempVars!tlevel = TempVars!tlevel + 1
    If TempVars!tlevel > 20 Then
       MsgBox "More than 20 levels exceeded!"
    Else
    
       If TempVars!tlevel = 1 Then
           strSQL = "INSERT INTO tbomtbl ( xlevel, imitem, jmmatlqty, jmunits, jbtype, jbjob, jbsuff, sequence, NextJobMatlIDKey, tbubble, BOMSeq ) " & vbCrLf & _
            "SELECT tempvars!tlevel AS [Level], Jobmatl.item, Jobmatl.[matl-qty], Jobmatl.[u-m], Jobmatl.[ref-type], Jobmatl.[ref-num], Jobmatl.[ref-line-suf], Jobmatl.sequence, Jobmatl.MatlNextJobIDKey, Jobmatl.Bubble, Jobmatl.[bom-seq] " & vbCrLf & _
            "FROM tbomtbl INNER JOIN Jobmatl ON tbomtbl.TJobIDKEY = Jobmatl.MatlTopJobIDKey " & vbCrLf & _
            "WHERE (((tbomtbl.xlevel)=0));"
            
            DoCmd.RunSQL strSQL
        Else
            strSQL = "INSERT INTO tbomtbl ( xlevel, imitem, jmmatlqty, jbtype, jbjob, jbsuff, sequence, TJobIDKEY, NextJobMatlIDKey, jmunits, tbubble, BOMSeq ) " & vbCrLf & _
                "SELECT tempvars!tlevel AS [Level], Jobmatl.item, Jobmatl.[matl-qty], Jobmatl.[ref-type], Jobmatl.[ref-num], Jobmatl.[ref-line-suf], Jobmatl.sequence, tbomtbl.TJobIDKEY, Jobmatl.MatlNextJobIDKey, Jobmatl.[u-m], Jobmatl.Bubble, Jobmatl.JobMatlIDKey " & vbCrLf & _
                "FROM tbomtbl INNER JOIN Jobmatl ON tbomtbl.NextJobMatlIDKey = Jobmatl.MatlTopJobIDKey " & vbCrLf & _
                "WHERE (((tbomtbl.xlevel)=TempVars!PrevLevel));"
            
            DoCmd.RunSQL strSQL
        End If
        
    'Loop Until DCount("JobMatlIDKey", "jobtablerecordsQRY") = 0
    End If
    strSQL = ""
    If TempVars!tlevel < 20 And DCount("JobMatlIDKey", "jobtablerecordsQRY") > 0 Then GoTo Nextlevel
  
    
End Function
Go to the top of the page
 
JAchord
post Apr 6 2020, 01:11 PM
Post#14



Posts: 227
Joined: 11-July 14



OK found a way to get an order kind of. So each item has a sequence number. So to get it to pull the order I can generate a number like 146971-10 which would be the top level. The next level would be 146971-10-1. After that, it shows me 146971-10-10 instead of 146971-10-2. This is a text field obviously. The first number is the id key. The second is the sequence of the top-level item. The third number is the sequence of the next level. So five levels would be 146971-10-1-1-1-1-1. Putting each sequence in a separate field does not get it to sort correctly either.

Any ideas of how I could get it to sort correctly?
Go to the top of the page
 
FrankRuperto
post Apr 6 2020, 03:54 PM
Post#15



Posts: 968
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
Putting each sequence in a separate field does not get it to sort correctly either.

That's because you're sorting a text field. You would have to zerofill the item and all sublevels in the text field for it to sort properly. You can always concatenate the separate numeric fields when displaying in forms or printing in reports. In your queries you would always be sorting this way:

CODE
ORDER BY Item, SubItem1, SubItem2, SubItem3...

Sort each numeric field separately, like above. You can always display and print them as a concatenated text field afterwards.

CODE
txtDisplayConcat = Item & "-" & SubItem1 & "-" & SubItem2 & "-" & SubItem3 ...

My pawn app uses separate numeric fields that get concatenated when they're displayed and they always sort correctly. Each contract consist of a lot that contains 1 to 6 items.
This post has been edited by FrankRuperto: Apr 6 2020, 04:40 PM
Attached File(s)
Attached File  ConcatNumFields.PNG ( 74.09K )Number of downloads: 5
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Apr 7 2020, 07:10 AM
Post#16



Posts: 227
Joined: 11-July 14



Frank, they are in number fields and are not text until I concantate them into a text field. The problem is when multiple fields are used the order is still incorrect or it does not land under the correct top item. The original DB used 5 different fields to set a sort order. There is one field that could have been used to set up the order for the complete bill of material but it was not used. It is important that I get the correct order because this is also the order of assembly.
This post has been edited by JAchord: Apr 7 2020, 07:11 AM
Go to the top of the page
 
projecttoday
post Apr 7 2020, 07:17 AM
Post#17


UtterAccess VIP
Posts: 12,168
Joined: 10-February 04
From: South Charleston, WV


As Frank mentioned just sort on the numeric fields.

Otherwise the only way to sort text-numeric fields is with leading zeroes (or leading blanks).

--------------------
Robert Crouser
Go to the top of the page
 
FrankRuperto
post Apr 7 2020, 08:09 AM
Post#18



Posts: 968
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (JAchord)
The problem is when multiple fields are used the order is still incorrect or it does not land under the correct top item.

If they're all numeric fields, I don't understand how it could NOT sort in the correct order if your query is using

CODE
ORDER BY ItemNum, SequenceNum, Level1, 2, 3, 4, ...

unless you imported the wrong data into one or more of the sort fields? e.g. the SequenceNum field has wrong data that belongs to one of the levels, etc. This is where knowing the data well helps, so analyze what you are importing into Access to see if the correct data is being imported into the correct fields.

hth, Frank
This post has been edited by FrankRuperto: Apr 7 2020, 08:16 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post May 7 2020, 09:24 AM
Post#19



Posts: 227
Joined: 11-July 14



Everyone, Thanks for the help. I slept on it for a while and came back to it. So I managed to get the sequence formated like 1.1.2.3.4. Each time the query looped it would add a decimal place. Then I came accross http://www.UtterAccess.com/forum/index.php?showtopic=2034741. This solved my sorting issue and works perfectly. Just updating this incase someone else is looking for a similar solution.

This will let the first item cascade to the child items attached to it. So if item one has a sequence of 3 it would be 3 on the BOM Sequence. If the child item for that item had the sequence of 1 then the BOM Sequence would be 3.1. The following item could have a sequence of 3 so it would be 3.1.3 now. Once converted to the array it would be 03, 0301, 030103.
This post has been edited by JAchord: May 7 2020, 09:29 AM
Go to the top of the page
 
projecttoday
post May 7 2020, 10:45 AM
Post#20


UtterAccess VIP
Posts: 12,168
Joined: 10-February 04
From: South Charleston, WV


I agree with Frank. Why concatenate and then sort. Just sort one level after another.

--------------------
Robert Crouser
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 06:52 PM