Need to do query update!

[tsvetkovdimitar]

Hi, please help me. I am struggling with my project. I built a database for a bridge management system and I need to update the inspection visits frequencies for a several component types on two separate dates. I know it has to be done by query, but I have no idea how. If anyone has any idea or advise please share it. I will be extremely grateful. Thank you!

[June7]

Why does it have to be done by query? Why not open form and go to the record(s) that need to be edited?

Unless there is a value common to the records that can be used to filter them from the rest of the records, must edit each individually.

[tsvetkovdimitar]

I have no idea why with query. I asked a friend and he said that the best way is to do it by query, but he didn't explain it in more details because we didn't have more time to talk about it. But I think I'm going to find out soon. Thank you

[ItsMe]

You friend may be referring to an action query. One type of action query is an UPDATE query.

In your situation, It sounds like a user will need to type the information into the database. After an inspection is done, the report is handed to someone for data input.

If this is the case, a form will most likely be adequate. Form's can be bound to fields. The action of typing in a field will update said field.

You can start by creating a select query and saving it. You can use the wizard to build a form. Bind the form to the query you created. If you can type information into the query while viewing it, your form should function just fine.

[tsvetkovdimitar]

Hi, thank you for responding to my problem. I think this will help me to solve it. Do you think that can apply to the following condition:
From time to time, the organization may decide to change the inspection frequency for a list
of component types on bridges of selected construction types. These inspection policy
changes are specified as in increase or decrease in the inspection interval (in months),
subject to an allowable minimum and maximum interval for each combination of
component type and construction type. For example, it might be decided to extend the
inspection interval for the cables on all cable-stayed bridges by adding 6 months to the
current interval per bridge, subject to a minimum of 12 months and a maximum of 36
months. If yes, that would be great. Thanks!

[ItsMe]

Well, Access is not going to force an inspector to do their job if the inspector decides to swing by the house and watch soap operas all afternoon.

You can program Access to offer reminders to the user on intervals of dates. You could program so if a specific component is out of its inspection parameter it notifies you. Notifications and such are typically considered to be advanced programming techniques.

I look at Access as having two sides to it. One side is the RDBMS side. It has tables that manage your data. You input data into the tables and then you can query the data. The RDBMS manages this. Then there is the application side. The application side offers a user interface. You can program your application to do many things.

For instance, if a bridge component falls out of inspection range, you could program the application side to recognize this and then take action. The application can open other programs, communicate over the internet, make a phone call, send a text message, etc. If your computer can do it, chances are, you can program your application to do it when the application recognizes a predetermined set of criteria is met. You could have the program launch a video of the boss dancing across a stage with a hat and cane every time a component needs to be inspected. Not related to Access by itself. It is the program that initiates it or invokes another program to do something.

[tsvetkovdimitar]

Lol I am still too far from doing things like that. I didn't realize that this could be so complicated. Actually what I should do in other words is :
Develop and test an SQL procedure to apply an inspection policy update to all
affected bridge components. This should apply all updates for a particular review
date to all affected bridge components in a single run of the procedure. If the
minimum or maximum limits for a particular component would be breached by
an update, the update should not be rejected, but the inspection interval for the
component should be set equal to the minimum or maximum, as appropriate.
You should include some simple mechanism to prevent the possibility of
accidentally applying a change twice in succession. I obviously need more knowledge and experience in Access to do that, but the time is pushing me.
Appreciate your help a lot.

[ItsMe]

You probably would not need to run an action query. This is where your data tables being designed correctly come into play. If a category or component type has a schedule change, you update the component category, not each component in each bridge in each city. You simply go to the component "Description" and change the value there. You would only UPDATE one record. No need to write SQL for that. Go to a form. The form looks at a recordset. Find the record for that component "Description". Change the inspection schedule.

Now when an inspector looks at a specific bridge, they will know if the components the bridge is comprised of are within the window or not. One record for each component description, or class, or part number, or lot number or whatever distinguishes the component's inspection schedule.

[tsvetkovdimitar]

Thanks I'm going to try it right away . I think I have an idea what to do now, thank you!

[ItsMe]

It is all about getting the correct relationship for the component description to the actual component in a bridge. The component in the bridge might have a unique serial number or primary key in Access. That record, the one with the serial number for the unique part, would hold a foreign key for the description for the component. The foreign key represents the Primary Key for the description.

[June7]

That works if a component has the same inspection requirements for every bridge that has the component. If there is requirement for variance, that is different issue.

[ItsMe]

That works if a component has the same inspection requirements for every bridge that has the component. If there is requirement for variance, that is different issue.

Yup, there may need to be a further break down of lot numbers or what ever the variance is based upon. It really is a generalization.