Import data, add column of data, refresh data, keep changes

[deerhntr22]

jdraw...right now the supervisor looks at a datagridview (DGV) in a VB program...he scrolls through the lines and adds his comments...when he hits the save button, the program stuffs the data into the "Old" table and creates the HTML file...next time the supervisor wants to make changes...he hits the refresh button and it starts the process all over again...(again, that is how it was supposed to work...but the records keep getting jacked up)(but I like Minty's idea)

What comes from the ERP is the data of what orders are shipping...(which is always changing...new orders added...orders that are put in the system as shipped are deleted)...other than that...no, nothing is needed or added to the ERP data...I actually could query the data myself, but the system does it already, so why reinvent the wheel.

Right now, when I replace the table...my data shifts...and either the comments are not aligned properly with the correct line data or it messed up in another field.

 

[jdraw]

I agree with Minty's approach. That's basically what I was working toward, but I wanted to make sure nothing was falling through the cracks. You need some field or combination of fields that makes each record unique. That is needed so that comments and any other edits are always associated with only that record.

I was trying to mock up your scenario. I used table InERP to be the data coming from the ERP system, and InLCL to be the Local (Access) table with the fields making a record unique and the Notes and any other fields that are required for Display to CustomerServices.

It seemed to me that
-records in InERP and not in InLCL would be inserted into InLCL.(new)
-records in InLCL not in the latest InERP would be deleted from InLCL since they have been deleted from the ERP system somehow.(dropped)
-records in InLCL that are also in latest InERP would be updated (if you edit some fields), Notes would not be Updated(keep whatever you have in InLCL); the matching would be done on the unique fields(combination of fields)

In this set up you would be managing the InLCL table (similar to Minty's Comments table).
You could still generate the HTML file from the InLCL table, each time the InERP extract is made, as the first step. That would contain the latest InLCL data with any Comments/Notes/edits done since the last cycle.

This could all be automated. There may be details we've missed, but I think this is much along the lines of what Minty was suggesting as well.

 

[deerhntr22]

I did a test run of it as well...and I am good until I get to joining "two" fields...

Right now I have one join property that shows all records from "New" (linked data from ERP text file) and only records from "SHIPCOM" (Table containing three fields...SO#, LINE, and COMMENTS) that are equal...

That works...except if the SO# has multiple LINE numbers...then the comment is displayed on all the lines...sooooo

I tried to add another join...doing the same thing...except looking at the LINE fields...and when there are two joins, the query doesn't show the "COMMENTS"

So close....:banghead:

 

[jdraw]

Show us the SQL for your join.

 

[deerhntr22]

I changed the names of the Tables to reflect what they are...

OPENORD = (ERP data for open orders)
SHIPCOM = (COMMENTS)

Here is the query with the "one" join that works unless there are multiple lines...then the comment shows for each line...

SELECT OPENORD.CUSTOMER, OPENORD.TY, OPENORD.[DATE ENTERED], OPENORD.[DATE DUE], OPENORD.[SO#], OPENORD.LINE, OPENORD.[WO#], OPENORD.[PART#], OPENORD.UM, OPENORD.DESCRIPTION, OPENORD.[ORD QTY], OPENORD.POUNDS, OPENORD.[WO CMP/ALLOC], OPENORD.LATE, SHIPCOM.[Reason/Actual Ship Date]
FROM OPENORD LEFT JOIN SHIPCOM ON OPENORD.[SO#] = SHIPCOM.[SO#]
WHERE (((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null Or ((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null)));


Here is the query once I add in the "second" left join...it doesn't show the comments at all....

SELECT OPENORD.CUSTOMER, OPENORD.TY, OPENORD.[DATE ENTERED], OPENORD.[DATE DUE], OPENORD.[SO#], OPENORD.LINE, OPENORD.[WO#], OPENORD.[PART#], OPENORD.UM, OPENORD.DESCRIPTION, OPENORD.[ORD QTY], OPENORD.POUNDS, OPENORD.[WO CMP/ALLOC], OPENORD.LATE, SHIPCOM.[Reason/Actual Ship Date]
FROM OPENORD LEFT JOIN SHIPCOM ON (OPENORD.LINE = SHIPCOM.LINE) AND (OPENORD.[SO#] = SHIPCOM.[SO#])
WHERE (((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null Or ((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null)));

 

[deerhntr22]

I had the criteria listed twice at the end of the SQL statement...I fixed that...

WHERE (((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null));

 

[jdraw]

So is it working now?

 

[deerhntr22]

Still doesn't work...this is what I am trying..."two left joins"

SELECT OPENORD.CUSTOMER, OPENORD.TY, OPENORD.[DATE ENTERED], OPENORD.[DATE DUE], OPENORD.[SO#], OPENORD.LINE, OPENORD.[WO#], OPENORD.[PART#], OPENORD.UM, OPENORD.DESCRIPTION, OPENORD.[ORD QTY], OPENORD.POUNDS, OPENORD.[WO CMP/ALLOC], OPENORD.LATE, SHIPCOM.[Reason/Actual Ship Date]

FROM ((OPENORD
LEFT JOIN SHIPCOM ON OPENORD.[SO#] = SHIPCOM.[SO#])
LEFT JOIN SHIPCOM ON OPENORD.[LINE] = SHIPCOM.[LINE])

WHERE (((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null));


***Says*** JOIN expression not supported
Then highlights "OPENORD.[LINE] = SHIPCOM.[LINE]" in second left join

 

[jdraw]

Instead of

FROM ((OPENORD
LEFT JOIN SHIPCOM ON OPENORD.[SO#] = SHIPCOM.[SO#])
LEFT JOIN SHIPCOM ON OPENORD.[LINE] = SHIPCOM.[LINE])

Try this (you have 2 tables, so only 1 JOIN statement involving 2 pairs of fields)

FROM OPENORD LEFT JOIN SHIPCOM ON
OPENORD.[SO#] = SHIPCOM.[SO#] AND
OPENORD.[LINE] = SHIPCOM.[LINE]


Here is similar set up I have in my mock up. This is part of my insert records from InERP into InLCL.

SELECT InERP.SOrder, InERP.SLine, InERP.ERP1, InERP.ERPXX
FROM InERP LEFT JOIN InLCL ON (InERP.SLine = InLCL.SLine) AND (InERP.SOrder = InLCL.SOrder)
WHERE (((InLCL.SOrder) Is Null) AND ((InLCL.SLine) Is Null));

 

[deerhntr22]

SELECT OPENORD.CUSTOMER, OPENORD.TY, OPENORD.[DATE ENTERED], OPENORD.[DATE DUE], OPENORD.[SO#], OPENORD.LINE, OPENORD.[WO#], OPENORD.[PART#], OPENORD.UM, OPENORD.DESCRIPTION, OPENORD.[ORD QTY], OPENORD.POUNDS, OPENORD.[WO CMP/ALLOC], OPENORD.LATE, SHIPCOM.[Reason/Actual Ship Date]

FROM OPENORD LEFT JOIN SHIPCOM ON
OPENORD.[SO#] = SHIPCOM.[SO#] AND
OPENORD.[LINE] = SHIPCOM.[LINE]

WHERE (((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null));


Did not pull the "comments"...[Reason/Actual Ship Date]...

 

[jdraw]

Hmmm.

Try adding this to your Where clause

OR SHIPCOM.[Reason/Actual Ship Date]>""


Trying to select Data where there is a value in this fields

To get the new info from InERL and the records with Notes in InLCL this is my mockup sql

SELECT InERP.SOrder, InERP.SLine, InERP.ERP1, InERP.ERPXX, InLCL.SLine, InLCL.SOrder, InLCL.Note
FROM InERP LEFT JOIN InLCL ON (InERP.SLine = InLCL.SLine) AND (InERP.SOrder = InLCL.SOrder)
WHERE (((InLCL.SLine) Is Null) AND ((InLCL.SOrder) Is Null)) OR (((InLCL.Note)>""));

 

[deerhntr22]

That did no change...same error...I believe it has something to do with multiple LEFT JOINS...I was reading somewhere that they had to be "nested" in order for them to work in Access...but I am unfamiliar on how to do that.

 

[jdraw]

I think we may be talking about different things????

You are working with 2 tables that have 2 pairs of fields in the join clause.
There is only 1 Left Join statement.

Please post the latest SQL.

In my mockup, InERP is the data coming from the latest ERP extract.
InLCL is where I'm managing the Processed edits/ comments/notes.

My latest sql in POst 31, gets the info from InERP (new data) and existing data in InLCL where there is/are Notes.

Have to go out foe a few hours. I'll be watching.

If you can post a small sample of your db that would help.

 

[deerhntr22]

Latest SQL....

SELECT OPENORD.CUSTOMER, OPENORD.TY, OPENORD.[DATE ENTERED], OPENORD.[DATE DUE], OPENORD.[SO#], OPENORD.LINE, OPENORD.[WO#], OPENORD.[PART#], OPENORD.UM, OPENORD.DESCRIPTION, OPENORD.[ORD QTY], OPENORD.POUNDS, OPENORD.[WO CMP/ALLOC], OPENORD.LATE, SHIPCOM.[Reason/Actual Ship Date]

FROM OPENORD LEFT JOIN SHIPCOM ON (OPENORD.[SO#] = SHIPCOM.[SO#]) AND (OPENORD.LINE = SHIPCOM.LINE)

WHERE (((OPENORD.LATE)<6 Or (OPENORD.LATE) Is Null)) OR SHIPCOM.[Reason/Actual Ship Date]>"" ;


Link to jpeg...it has sample of my tables and data

https://www.dropbox.com/s/hjtl8tp79ru2626/TBL.jpg?dl=0

 

[Minty]

I assume your LINE number is a text field in which case you have 001 in one and 0001 in another - these aren't equal...

 

[deerhntr22]

That was it minty......thank you...

 

[jdraw]

Just checking back and it seems things are working. What is the latest status?
Have you got it all automated???