append questions in vba

J

jasn_78

Registered User.
Local time
Tomorrow, 08:13
Joined
Aug 1, 2001
Messages
214
hey guys what i have is atm is a sgl table that i import a csv file into call tblAJLTEMP which all works fine i have even managed to import the first row which is a sales header into a table and convert the multiple date fields into 1 etc my problem lies with the rest of the data.

basically the csv file is a journal (sales data) for all registers NB i dont have control over what is exported from that as its 3rd party.

there are a series of headings
H = HEADER
D = TABLE NUMBER
C = CASHIER NUMBER
P = PRICE LEVEL
I = ITEM NUMBER (PLU)
T = TENDER TYPE (CASH/EFTPOS ETC)
R = ROUNDING
A = ACCOUNT
M = MEMBER
of which i am ok with table structure etc my problem is i need to combine multiple rows e.g the "C" line the "P" line and the "I" line into one record i think my logic which i have posted below is fine but just not sure on the code for saying do this loop for each record until next header line and how to combine the actual fields of "C","P" and "I" into one record
Code: 
WHERE FIELD1 = H
	UNTIL FIELD1 = H AGAIN
		SELECT CASE FIELD1
(THE SALESTBL CONTAINS AN AUTO NUMBER FIELD CALLED SALESID THAT I AM USING AS THE PRIMARY KEY FOR THE SALES DETAIL AND TO LINK TO ALL OTHER TABLES)					CASE = H
			QRYUPDSALESTBL
			CASE = D
			QRYUPDTABLENUMBERTBL
				MAKE SALEID SAME AS SALEID IN QRYUPDSALESTBL
(NOTE ALL THE NEXT FIELDS NEED TO GO INTO ONE RECORD)
			CASE = C
			DO UNTIL UNTIL NEXT TIME FIELD1= C
			QRYUPDCASHIERITEMSALESTBL
				MAKE SALEID SAME AS SALEID IN QRYUPDSALESTBL
				IF FIELD1 = P 
					QRYPLRICELEVELITEMSALESTBL
					DO UNTIL NEXT TIME FIELD1=P
					IF FIELD1 = I 
					QRYUPDPLUITEMSALESTBL	
					IF FIELD1 = S
					QRYUPDSTRINGTBL
				END IF (FOR THAT PRICE LEVEL)
(THERE WILL ALWAYS BE AT LEAST 2 OF THE NEXT RECORDS 1 AS TAX ONE AS MEDIA TYPE)			
			CASE = T
			QRYUPDTENDERTBL
			MAKE SALEID SAME AS SALEID IN QRYUPDSALESTBL
			CASE = R
			QRYROUNDINGTBL
			MAKE SALEID SAME AS SALEID IN QRYUPDSALESTBL
			CASE = A
			QRYUPDACCOUNTTBL
			MAKE SALEID SAME AS SALEID IN QRYUPDSALESTBL
			CASE = M
			QRYUPDMEMBERTBL
			MAKE SALEID SAME AS SALEID IN QRYUPDSALESTBL
		END SELECT
	NEXT LOOP
END


note this is just my logic and not actual code im not after help with all the code just an opinion on if im heading in the right direction and the functions i should be investigating for the couple of questions earlier.

thanks in advance
jason
 
The easiest way to "compile" difficult data like this is to use VBA coding.
Import into a staging table or open the file directly in VBA, that doesnt matter much.

If you havent done anything like this lookup "DAO.Recordset" in the help and on the forum, this will get you started.
 
namliam thanks mate i have made a start to this just getting a problem where i am in a continuous loop on my first part of the code

any ideas?
Code: 
Dim DB As DAO.Database
Dim Rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim tbl As String
Dim strSQL As String

tbl = "tblAJLTEMP"
Set DB = CurrentDb
Set qdf = DB.QueryDefs("qryupdSALESTBL")
Set Rs = DB.OpenRecordset(tbl, dbOpenTable)
Do Until Rs.EOF
If Rs![Field1] = "H" Then
    strSQL = "INSERT INTO tblSALES (TRXTILLID, TRXNUMBER, TRXTIME, TRXDATE, TRXLOCATION) " & _
             "SELECT tblAJLTEMP.field2, tblAJLTEMP.field3, " & _
             "(tblAJLTEMP.field4 & ':' & tblAJLTEMP.field5), " & _
             "(tblAJLTEMP.field6 & '/' & tblAJLTEMP.field7 & '/' & tblAJLTEMP.field8), " & _
             "tblAJLTEMP.field9 " & _
             "FROM tblAJLTEMP WHERE (((tblAJLTEMP.field1)='H'));"
            
            qdf.SQL = strSQL
            DoCmd.OpenQuery "qryupdSALESTBL"
End If
Rs.MoveNext
Loop
 
Hmz, It is not your loop that is the problem, but rather your query... You are inserting ALL "H" records every time you find 1 "H" Record.

I wouldnt do it like that anyway...
You off to a decent start tho...

If I need to process several record "types" I generaly do something like:
Semi quick pseudo code, so bare with me...
Code: 
Set Rs = DB.OpenRecordset("tblAJLTEMP", dbOpenTable)
Set RsTarget = DB.OpenRecordset("tblSales", dbOpenTable)
Do while not rs.eof
    If rs!Field1 = "H" then 'if this is the H line, do stuff
        rsTarget.update ' Close the previous record
        rsTarget.addnew ' Start a new record
        rsTarget!Somefield = rs!Somefield
        rsTarget!Otherfield = rs!Otherfield
    elseif rs!Field1 = "D" then
        'Do some stuff here
    elseif rs!Field1 = "C" then
        'Do some stuff here
    elseif rs!Field1 = "B" then
        'Do some stuff here
    elseif rs!Field1 = "A" then
        'Do some stuff here
    else
        'Do some stuff here
    endif


    rs.movenext
loop
 
namliam thanks for your help with this just a question on using if statements for what i want to do. Since i have multiple records in my table in the following order or close to.

H
D
C
P
I
I
I
T
T

that gets repeated for each sale will it go through each if statement first or will it do all the code for all the H's first? the reason i ask this is i need to assosciate all data after the h and before the next one with the sale id for that H which if i use Dmax() should work fine to me but just wanting to see if im thinking about this the right way

sorry i know these are probably basic questions but new to the whole programming thing and just want to make sure im not wasting my time or any1 elses.
 
unless your data is related in some way, you probably CANT SAFELY do all the H's first

If you do all the H's first. when you get the first D, how do you know which H it relates to, unless there is a "H" key included in the D record.

Generally the sort of structure you have includes intrinsically UNRELATED items, grouped together, and in a MANDATORY order.

Thus you have a
Header
then whatever a DCP are
then 4 Item records
then 2 trailer records

these will probably ONLY make sense if you process them in order.

---------
in fact, because of this, you should be careful about loading them into a table, as you may find the order is not maintained by the process

it may be better for instance to read the records in a row at a time, and process them as you do, with vba.

ie

Code: 
while not eof(infile)
   readline
   select case firstchar
      "H"; process a new record
      "T": finalize new record
      "I": process an item
      etc
    end select
wend
 
Much like Gemma says...

It is a little dependant upon how fixed the formatting is or how flexible ....
And how you want to store your information... I could see a point for the I's to go into a seperate table...

@Gemma
Did you notice I gave pretty much the same sample you gave??
 
yes, although you are processing the imported table, rather than the text file

i may be wrong, but I am fairly sure access does not necessarily respect the order of a loaded file - i import files/csvs etc, and the order in the raw table is sometimes not the same as it was in the loaded file.
 
gemma-the-husky said:
i may be wrong, but I am fairly sure access does not necessarily respect the order of a loaded file - i import files/csvs etc, and the order in the raw table is sometimes not the same as it was in the loaded file.
Click to expand...

A query -by its nature- returns records in -more or less- Random order. Yes... Tho in general 99% of the time, the records are returned in the order they were entered... but you are absolutely right, you cannot rely on it.

To enforce the order it was imported, add an autonumber to your "staging" table and order on that.

I was starting from the premisse that you stage the import file first. This is "in general" easier IMHO than processing a text file, because you have more control over where you are and what you do with a table rather than the text file.
Even more so... I have "learned" the habit of creating a junction table between the Original text file staging table and the destination tables. This way IF ever something goes wrong it is a simple query to compare the values in the table to the ones in the original file.
Where if you only have a flat textfile to rely on you have to go and search and stuff, which sometimes can be a pain.

In some cases you really do also need the original line to stay in tact, in which case this too is really an easy way of doing things.
 
gemma and niam i do import to a staging table first "tblajltemp" then an running the recordset code from that table, gemma in respone to ur msg i def DONT want to run each H first for that exact reason was just making sure it wouldnt which u later confirmed it may not always do it in the right order so i might have to look into that. as for auto numbering is there a way of ressetting that each time in the tblajltemp table to 1 each time as once i have imported my info to the relevant tables i am deleting eveything from the staging one and just would think it would look nicer if it went back to 1 each time

thanks for ur help guys and girls i cant believe i've come so far already:)
 
jasn_78 said:
gemma and niam ur msg i def
Click to expand...

Would it hurt to just type "normal" stuff? I get what you mean but man what a headache....

If you are using staging table(s) you will want to compact your DB each time to prevent access from "bloating" (using more space than it needs).

Preverably you would have a Backend database where your staging happens. This external database you can then compact using the command line without problems or even the user noticing.

Upon compacting the autonumbers reset automagicaly to the lowest number possible. In case of an empty table that would be 1.
 
namliam said:
Upon compacting the autonumbers reset automagicaly to the lowest number possible. In case of an empty table that would be 1.
Click to expand...
That used to be the case in A97,A2000 and I think A2002(XP) but I have noticed in A2003 that compact and Repair does not reset the Autonumbers. This also applies to A2007. See this link for more info
 
that gets repeated for each sale will it go through each if statement first or will it do all the code for all the H's first? the reason i ask this is i need to assosciate all data after the h and before the next one with the sale id for that H which if i use Dmax() should work fine to me but just wanting to see if im thinking about this the right way
Click to expand...

sorry i misunderstood - i thought you WANTED to process all the H's first etc.

--------
what mailman said was open this table as a recordset and process it line by line

what i said was it was possible that Access changed the order from the order in the input file. If so, it might not be safe to process the table directly line by line, as your processing probably depends on the records being in a particular order.

In which case either put a numerical sequence in the excel file, as mailman says, then you are assured of being able to obtain the correct order, or process the text file directly.

------------
after you do the import, before you process it, open the table, and see if the order has changed.
 
sorry namliam was late out night here when i read that msg bit tired and wasnt thinking straight
 
Ok another questions guys sorry bout all the questions but have the record set case statement working great except for one factor i need to be able to merge a couple of rows into one row in another table

Code: 
Select Case Rs!Field1
    Case "H"
        RsSales.AddNew ' Start a new record
        RsSales!TRXTILLID = Rs!Field2
        RsSales!TRXNUMBER = Rs!Field3
        RsSales!TRXTIME = (Rs!Field4 & ":" & Rs!Field5)
        RsSales!TRXDATE = (Rs!Field6 & "/" & Rs!Field7 & "/" & Rs!Field8)
        RsSales!TRXLOCATION = Rs!Field9
        RsSales.Update ' Close the previous record
    
    Case "P"
        RsItems.AddNew
        RsItems!PRICELEVEL = Rs!Field2
        RsItems!SALESID = DMax("tblSALES.SALESID", "tblSALES")
        
        Select Case Rs!Field1
            Case "C"
            RsItems!CASHIER = Rs!Field2
            RsItems!SALESID = DMax("tblSALES.SALESID", "tblSALES")
        End Select
        
        RsItems.Update
    
    Case "D"
        RsTables.AddNew
        RsTables!TABLENUMBER = Rs!Field2
        RsTables!SALESID = DMax("tblSALES.SALESID", "tblSALES")
        RsTables.Update
       'Case Else
       'MsgBox ("this didnt work")
    End Select

So what i am trying to do is i have a table referred to in here as RsItems that i need to contain both the CASHIER and the PRICELEVEL for that sale in the same field i know that this really isnt how it should be done with databases but in this case i really dont have a choice due to requiring all parts of the sale in one record so any ideas would be great

Cheers
Jason
 
so what is wrong?

you will have two different fields set to the same value - but if thats what you want, your code will do it. Are you trying to do something else?
 
gemma sorry about my explanation before what i need to is import the data below

Code: 
H, 14, 26, 14, 22, 30, 03, 2008, 0,
P, 1
D, 1
C, 1,
C, 99,
P, 1
I, 10462, 1, 7.90,
I, 10463, 1, 8.90,
C, 1,
P, 2
I, 10462, 1, 7.90,
I, 10463, 1, 8.90, 
C, 99,  ,
P, 1
I, 10460, 1, 0.60,
P, 3
I, 10471, 1, 16.90,
P, 1
I, 10467, 1, 12.90,
T, 65496, -5.82
T, 1, 64.00

so the fields with
Code: 
C, 99,
P, 1
I, 10462, 1, 7.90,
I, 10463, 1, 8.90,
i need to import as two lines in the tblITEMSALES table as field1:99 field2: 1 field3: 10462 field4: 1 field5: 7.90
then a 2nd line of field1:99 field2: 1 field3:10463 field4: 1 field5: 12.90

i hope this make more sense
thanks
jason
 
You can just make it part of your normal case statement

It also looks like your C and P lines are like headers while the I lines are "Items"

If so, just store your C and P lines/values into a Variable, then use that variable to store into a table on each I line
Code: 
Case "C"
    txtC = rs!Field2
Case "P"
    txtP = rs!Field2
Case "I"
    rs.addnew
    ' fill record
    rs.update
end select

Sorry for the semi pseudo code, but I hope you can fill in the blanks :)

Note:
You are doing a DMAX, the same DMAX 3 times.
Considering the following
1) Dmax is VERY slow
2) You allready have (I think) the tblSales in rsSales
3) It is 3 times exactly the same
I think you can come up with something better/faster/more effecient and easier to maintain than this ;)

Good Luck :D
 
namliam i could just kiss u :) lol well if you are a good looking girl i could:)

thanks mate that worked perfectly i am so grateful to everyone on here for there help with this :)
 
Post your code for posterity?? And offcourse final analasys ;)
 
You must log in or register to reply here.

Similar threads

T
Problem with NAME Statement
Replies
2
Views
499
Mark_
M
M
Solved Duplicate value check - complicated.
Replies
16
Views
2,261
Pat Hartman
P
J
Using variables as field names
Replies
17
Views
1,211
Jackdaw
J
M
Solved Update records through a recordset
Replies
11
Views
1,081
Pat Hartman
P
K
Self-Healing Ribbon Pointer
Replies
0
Views
469
KevinBaker
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom