Table gets overwritten often, but need to relate items to another tbl

[vlbridge]

I have two tables. One is for jobs that require a boat to be down for repairs. The other is a list of Purchase Orders. I would like to be able to somehow select which purchase orders are associated with the job. The problem is, the purchase order list gets overwritten twice a week because it is exported from our purchasing software and as the estimate changes to an actual and the status changes from open to history, the table needs to be updated with the correct information. Can anyone think of a method to do this? I'm not the most proficient in Access, so any help would be appreciated. Thanks in advance.

 

[Andrzejek]

Looks to me you have a '[blue]problem[/blue]' and a '[green]sollution[/green]' to it in the same sentance:

"The problem is, [blue]the purchase order list gets overwritten twice a week [/blue]because it is exported from our purchasing software and as the estimate changes to an actual and the status changes from open to history, [green]the table needs to be updated with the correct information.[/green]"

So, don't overwrite data in your table, just update it.

Have fun.

---- Andy

 

[vlbridge]

How? There are thousands and thousands of purchase orders. And, it would need to be updated at least twice a week. Is there a way to have access look at an excel spreadsheet and update the table with the changes?

 

[Andrzejek]

Yes.
You can have a little code in VBA in Access that 'looks' at the Excel spreadsheet one row (one record) at the time and updates the corresponding record in your Access table.

You can also mark which records were updated, if you want to.

If you do not find the corresponding record in your table, insert a new record in your table.

Have fun.

---- Andy

 

[Andrzejek]

The other question is: how do you relate the two tables now?

Do you have some kind of Key to join the tables? Like a BoatID that is unique in Boad table (a Primary Key), and BoatID in PO table (Foreign Key)?



Have fun.

---- Andy

 

[vlbridge]

I have a Vessel Table, where the Vessel ID is the primary key. Then, the Vessel ID is a foreign key in both the jobs table and the PO table. One boat may have multiple jobs. I have a Job ID field that's the primary key in the jobs table. I assume I'd have to add that into the PO table as a foreign key. But this field is not going to be included in the Excel spreadsheet because that's coming straight out of the purchasing software. Is that going to be a problem? Any ideas?

 

[MazeWorX]

Import the spreadsheet into a Temp table do what you will with it with queries the update your records with an update query. Even with vba go so far as to fully automate the process

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Andrzejek]

Your tables have to relate to each other somehow. Looks like VesselID is something that you already have. But I am comfused here, you say:"the Vessel ID is a foreign key in both the jobs table and the PO table." and then you stated: "I'd have to add that (VesselID?) into the PO table as a foreign key." So do you have or don't you have VesselID in PO table?

And how do you join those tables right now - since you over-write entire PO table from the Excel spreadsheet twice a week? How does your Vessel Table relates to PO table now?

Have fun.

---- Andy

 

[vlbridge]

MazeWorx - sounds like that would work... problem is, I don't really know how to do those things... so I'll have to play around with it and figure all that out.

Andrzejek - I think you misunderstood what I said. The VesselID is in the table already. I am saying I would need to add the JobID to the PO table to relate it to the jobs table, since one vessel will have more than one job. And, I don't relate them yet. That is what I'm trying to do. I am trying to create this database. Currently, I just export from the purchasing software into excel, which overwrites the spreadsheet every time. Then, there's a separate excel spreadsheet for the jobs. I'm trying to get them both into one database so that we can see what PO's go to what job and the total spent on those jobs compared to the projected amount.

 

[Andrzejek]

Creating correct data base is half of the battle.
I don’t know what tables are you going to have, and how they will relate, but having JobID in Jobs table and in PO table sounds like a good idea. The question here is: how will you relate a row from Excel to the record in your PO table? Since you will not have JobID in Excel.
Is it one-to-one (PO record to Excel row) relationship? Or if you need: 5 pieces for job 1, 2 pieces for job 2, and 7 pieces for job 3, so the Excel file states: 14 pieces needed? And it is not split into Job numbers?


Have fun.

---- Andy

 

[vlbridge]

And there lies the problem...

The Excel file (the export from the purchasing software) essentially IS the PO table in Access. However, if I'm going to relate the table to the Jobs, I will need to enter the JobID field to relate the two. The JobID is not going to be in the Excel file. The POs are not separated by job in the excel file at all. When someone is entering data about a job in Access, I want them to be able to select the PO's that are associated with it. So, I really am at a loss of how to accomplish this.

 

[Andrzejek]

Do you have any unique and consistent value in every record (row) in your Excel file? If so, you can use it to assign it to your JobID. You would have to do it once. I know you have 1000's of records, but you have to start somewhere...

If not, can you ask to have one create for you? I am sure the purchasing software works based on some kind of data from a data base, and it could have ability to expose / create a unique key for you.

Have fun.

---- Andy

 

[vlbridge]

I would say the PO# itself is a unique identifier, but some PO's have more than one line and when it exports, the PO# will appear for each line. So I guess the answer to your question is no. And, if there is a way to create one, I don't know how. I could look into it.

 

[Andrzejek]

Would multiple (the same) PO# belong to the same JobID? If no, there is a problem.

If yes, you may be OK. You would have to assign which PO#('s) belong to which JobID. I know there is a lot of records, but that woud have to be done once.

Can you have PO# that belongs to more than just 1 JobID?
Let's say 3 boats need GPS units, 2 units for each boat. So you have:
JobID Part
123 2
145 2
456 2

and you may just have one PO# that reads: We need 6 GPS units. :-(

Have fun.

---- Andy

 

[vlbridge]

Each line in the PO file will only be associated with one Job. I don't think you're understanding the problem. If I add another field to the PO table in Access, JobID, how will I be able to keep the PO table updated? The Excel file will not have the JobID field at all.

 

[Andrzejek]

What fields do you have in your tblPO (or whatever you call it)?
[tt]
tblPO
OrderID (PK)
JobID (FK related to JobID as PK in tblJobs table)
...[/tt]

and OrderID in your tblPO would relate one-to-one to PO in Excel when you get it twice a week from the purchasing software.

You would load data into tblPO once from Excel file, assign appropriete JobID and OrderID to each record (once) and you are done.

Next time when you get the Excel file, you would UPDATE tblPO (not over-write teh data), and INSERT new records.

Have fun.

---- Andy