Table gets overwritten often, but need to relate items to another tbl
Table gets overwritten often, but need to relate items to another tbl
(OP)
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.
RE: Table gets overwritten often, but need to relate items to another tbl
Looks to me you have a 'problem' and a 'sollution' to it in the same sentance:
"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."
So, don't overwrite data in your table, just update it.
Have fun.
---- Andy
RE: Table gets overwritten often, but need to relate items to another tbl
RE: Table gets overwritten often, but need to relate items to another tbl
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
RE: Table gets overwritten often, but need to relate items to another tbl
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
RE: Table gets overwritten often, but need to relate items to another tbl
RE: Table gets overwritten often, but need to relate items to another tbl
HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>
RE: Table gets overwritten often, but need to relate items to another tbl
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
RE: Table gets overwritten often, but need to relate items to another tbl
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.
RE: Table gets overwritten often, but need to relate items to another tbl
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
RE: Table gets overwritten often, but need to relate items to another tbl
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.
RE: Table gets overwritten often, but need to relate items to another tbl
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
RE: Table gets overwritten often, but need to relate items to another tbl
RE: Table gets overwritten often, but need to relate items to another tbl
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
RE: Table gets overwritten often, but need to relate items to another tbl
RE: Table gets overwritten often, but need to relate items to another tbl
What fields do you have in your tblPO (or whatever you call it)?
tblPO
OrderID (PK)
JobID (FK related to JobID as PK in tblJobs table)
...
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