Change table field text based on field in other table

[arcaidius]

(MS ACCESS)

Good afternoon, I am a complete noob on access. I do understand the basics, table set up, queries and forms and such. It's the code and functions that's getting me.

So lets take a crack at it, I have one table that will not receive any new entries, the primary key is auto numbered 1 to 86 representing the item number. The next column will be whether the item is in stock or missing, (Item will be replaced if missing with the same number) manually entered. The next column is whether the item is Issued, Available, or on Hold that I would like to automate based on Issued and returned dates from another table. If the item has an issue date only then it should be "Issued", If it has a returned date then "HOLD" until 6 months after the return date then "Available". The table with the dates will have many entries so it is a one to many relationship.

Is this possible? I have been trying to find it on my own with no luck.

 

[Micron]

First thing is to establish a connection to your data. Easiest is to link the table(s) to your front end (fe) database that will contain your queries, forms and reports (and any code/macros). Then create a query using the table(s) that allows you to edit the fields manually. If you can't do the edit manually, it cannot be done programmatically. Then you can create a form that displays the information and either automatically update fields or prompt a user to "allow" the update, or you can have the updates happen when the fe opens. However, either will require that the fe is opened, either by a user or by a scheduling app such as Task Scheduler. The point is, if neither occurs until 7 months after the return date, then that's when the update happens.

What you can do inside of tables themselves when it comes to updating fields is very limited. I'm pretty sure that altering the value of a field based on a field from another table is not one of them, thus the need for forms and queries. The way some of your first post reads, I suspect you need to review

UtterAccess.com

www.utteraccess.com

General: Use Autonumbers properly

and perhaps

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

and maybe even Table and PK design tips

Microsoft Access Tables, Primary Key Tips and Techniques

Optimally Defining Primary Keys in Microsoft Access Tables

www.fmsinc.com

 

[arcaidius]

Thanks,
I will check out those links and do some more self training. (If my boss only knew how much time I waste messing around with Access and Excel).