Update Query

[smacdonald23]

I'm wondering if somebody can help me.

I'm building a database for work. I have 2 tables which are called "Customer_ID" and "Ports".

The Customer_ID table has "CUST_ID, PORT, TICKET_ID"
The Ports table has "PORT, STATUS"

The tables are linked via "PORT".

I have a form with 2 subforms for both tables. Basically you create a new "Cust_ID" in the Customer_ID and then search for the next available "PORT" in the Ports table. From here My guys will copy the next port ID into the "PORT" field in the Customer_ID table then they click a button I want it to look at the PORT field in the CUSTOMER_ID table search for it in the PORT table and change the status from available to working.

Can this be done

 

[jdraw]

I suggest you read this article on relational database principles and research the links for Normalization found inside the article.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Good luck

 

[smacdonald23]

Hi Thanks for that, I've ok with the principles its just the code or query I need help on.

The port fields are the exact same. I require the code so it looks up field from Customer_ID field and if it matches the field on the Ports table it changes another field from available to working.

 

[pwbrown]

It's just a simple update query.
Add the appropriate fields / tables in the update query, check the two fields for matching values in the criteria then in the update to under status put "working".

 

[jdraw]

You are telling us HOW you have done/will do something. But we don't know WHAT you are trying to do. Please tell us in plain English, what you are trying to do.
As I have said many times before, pretend you are in a McDonald's line, you turn to the person behind you and youtell them your situation -- your environment, a brief clear description of the problem/opportunity you are facing and what you think you should do.
Remember that this person to whom you are talking has NO knowledge of you, your environment, NO familiarity with Access or database generally.
??? What would you say??? (remember we are the person in line behind you)

 

[smacdonald23]

Sorry about that.

I an wanting a query where it looks at 2 individual columns in 2 different tables. If they match I want the query to replace a different field which is the status. Does that make any sence.

 

[pwbrown]

UPDATE Ports
SET Ports.STATUS = "Working"
WHERE ((Ports.PORT) = (Customer_ID.Port))

It will be something like this.

 

[smacdonald23]

I forgot to update this yesterday but I manager to figure it out but Thanks pwbrown thats exactly what I used.