INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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: smile

"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

(OP)
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?

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

(OP)
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?

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

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>>

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

(OP)
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.   

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

(OP)
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.

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

(OP)
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.

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.  sad

Have fun.

---- Andy

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

(OP)
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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close