Alot of Help with redesign

[renenger]

I really need some help. I am supposed to have a rough working copy of the database ready by Friday and at this rate I am not going to have it done. I have an Access database now that we enter units in and track through our manufacturing plant to shipping. We are bringing an AS400 ERP system on line in two weeks that will replace this. The problem is we lose a lot of the tracking reports that we are accustomed to.

What I have been tasked to do is redesign our Access interface to work with the AS400. One of our programmer's has taken an AS400 report and exported it to a .csv file. I am using this file to update and insert into 2 Access tables. My problem is I need to automate this. I cannot get this to work. I have the code set under Frontier update on the menu that appears. My prepare for table query is no longer working. It was working in Access 2003 but I started getting an error on the main table update so I switched back to 2000 and now it not's working at all. I get an undefined function error with MID. Also, I am not sure of the best way to set this up. Users currently have an interface with the backend being on the server and all tables are linked. The way I am setting this up the users will be updating to a local table in their interface. Is this the best way? I need the update to occur every few minutes as the users will be updating in the AS400 and then Access will retrieve this new information from the .csv file be exported every few minutes. If I can do this on the backend that would be great, but I would need some kind of job scheduler right?

One of the other problems I am having is I need Access to create WOSD (Work Order Start Date) and WODD (Work Order Due Date). This is based on the DelDate, DrStyle and Prefin. For example, if the DrStyle is RP22, the the WOSD should be 6 days before the delivery skipping weekends. I am not sure how to get Access to update this when we do the update. If the deldate changes which is will (part of the reason for the update) then I need it to recalculate the WOSD and WODD as long as the Status in the Tracking table is In Contracting, In Layout, or Ready for Production anything past that point is already being built and the WOSD is not relative.

I would greatly appreciate any help I can get. I am still learning and will be taking some classes within the next month but I need to have this ready for testing ASAP.

Thank you!!!

 

[neileg]

Why are you importing the CSV file? Providing the format and the name of the files never changes you should be able to link to it.

Your WOSD and WODD appear to be dependant on the data in the CSV file (plus some constants) so you need to calculate these, not store them. That way your data will be based on the most recent version of the CSV without any need to keep importing.

Have a think about this before you work on the details.

 

[renenger]

Redesign help

Hi, I am using a .csv file because the company has locked down the use of ODBC and this report has everything combined that I need. That is my only option at this time.

Any help would be greatly appreciated. My problem with the WOSD and WODD is just that they are calculated fields. I have a query though that calculates the number of boxes we have scheduled to build on the WOSD. This allows us to determine the capacity that our plant is scheduled for at any given time so we can rearrange delivery dates if necessary so that we don't overload our manufacturing plant.

 

[renenger]

Figuring it out, need help with UPDATE

Ok I have figured out my update queries. I still need help with automating the .csv upload. Someone mentioned that I need to create a file spec. I do not know how to do this.

I am having a problem with my details table update query. My primary key fields on tblFrontierUnits are ProjectID, Phase, Unit, Tract, Release, UnitPlan, UnitOpt. As long as one of those fields has something different in it, it should allow it. My tblCSVImport shows these two units:

80506 ANTHONY 19 72 , , 419 , N N , PLAN 20 RO CC21 0
80506 ANTHONY 19 72 , , 419 REV N N , PLAN 20 RO CC21 0

I run a Make-Table query and it adds this information to it in tmptblFrontierUnits. I then have a select query that selects all of this information and formats the date. Both of these units are in this query as well. I then run an UPDATE query that updates or adds this information to the details table. However, it will only add the one that says REV. The first one does not update. Any ideas why? I can add it manually but it will not copy over on the update.

UPDATE qryPrepareImportData LEFT JOIN tblFrontierUnits ON (qryPrepareImportData.UnitOpt = tblFrontierUnits.UnitOpt) AND (qryPrepareImportData.UnitPlan = tblFrontierUnits.UnitPlan) AND (qryPrepareImportData.Release = tblFrontierUnits.Release) AND (qryPrepareImportData.Tract = tblFrontierUnits.Tract) AND (qryPrepareImportData.Unit = tblFrontierUnits.Unit) AND (qryPrepareImportData.Phase = tblFrontierUnits.Phase) AND (qryPrepareImportData.ProjectID = tblFrontierUnits.ProjectID) SET tblFrontierUnits.Deldate = [qryPrepareImportData].[DelDate], tblFrontierUnits.ProjectID = [qryPrepareImportData].[ProjectID], tblFrontierUnits.Phase = [qryPrepareImportData].[Phase], tblFrontierUnits.Unit = [qryPrepareImportData].[Unit], tblFrontierUnits.Tract = [qryPrepareImportData].[Tract], tblFrontierUnits.Release = [qryPrepareImportData].[Release], tblFrontierUnits.UnitPlan = [qryPrepareImportData].[UnitPlan], tblFrontierUnits.UnitOpt = [qryPrepareImportData].[UnitOpt], tblFrontierUnits.POCOMP = [qryPrepareImportData].[POCOMP], tblFrontierUnits.PrjFrm = [qryPrepareImportData].[PrjFrm], tblFrontierUnits.OrderNo = [qryPrepareImportData].[OrderNo], tblFrontierUnits.OrderStat = [qryPrepareImportData].[OrderStat], tblFrontierUnits.Boxes = [qryPrepareImportData].[Boxes], tblFrontierUnits.Species = [qryPrepareImportData].[Species], tblFrontierUnits.DrStyle = [qryPrepareImportData].[DrStyle], tblFrontierUnits.PreFin = [qryPrepareImportData].[Prefin]
WHERE (((tblFrontierUnits.ProjectID)=[qryPrepareImportData].[ProjectID] Or (tblFrontierUnits.ProjectID)<>[qryPrepareImportData].[ProjectID]));



Any ideas?

 

[neileg]

You haven't listened to my question! Why are you importing the CSV? Why not link the CSV file instead?

I think all this update query business is unnecessary. You don't need to store stuff in a table that has been calculated. Just use a select query instead of the table.

This data
80506 ANTHONY 19 72 , , 419 , N N , PLAN 20 RO CC21 0
80506 ANTHONY 19 72 , , 419 REV N N , PLAN 20 RO CC21 0
has more fields in the first record than in the second. Is this a typo?

 

[renenger]

Redesign Help

Thank you Neil!! Sorry I don't think I was fully understanding what you were getting at. I did link the .csv file and that will work out fabulously. However, I still need to use the update queries. The csv file will change as units are added and removed. I will need to keep history. I did figure out my problem though. One of my primary keys is UnitOpt in the detail table. That field was blank on some of my rows, so I had it put in a 0 if it was blank and that solved my problem.

Thanks so much for you help!!

 

[neileg]

Happy to help. I do understand if you need to keep a history.