Need help identifying what to do....?

[shands]

Hello all,



I've just taken a new job which requires administering several databases created in Access. I am not familiar with Access at all....but my new employer did send me to some intro courses last week to help get me started. They were good but seemed fairly general. I have been asked to "fix" a specific database (vehicle maintenance) where the admins update the information. My task is to have the database flag vehicles that are in service (i.e. for an oil change every 3000 miles) if they are getting close to needing another service (i.e. a tire rotation which is every 10,000 miles) in an effort to "kill two birds with one stone" and get the vehicle back on the road rather than have it come in twice for maintenance within a specific amount of time.

Being brand new to access I am a bit confused on how to get this done. The database as it stands now is comprised of 9 tables but no queries or forms. I am thinking that somehow doing conditional formatting might take me in the right direction, but I'm not certain. I want to build a nice form for the admins to input the data in, and hopefully as they do, they will receive a flag that identifies the other service needed soon, and they can put that order in.

My tables include information like odometer reading when it leaves the shop, and there is a table with some of the recurrent mileages listed for services, (3000, 6000, 7500, 10,000 miles, etc). Am I on the right track with this at all, or can this even be done?

[pbaldy]

I think you're in for more than a "fix". A database without queries and forms might be a good start, but it's not ready for prime time. My users never see tables or queries, just forms and reports. You have no control over them if they're directly in a table or query.

I've actually written a couple of vehicle maintenance databases. The type of thing you're after isn't terribly difficult, but it's not level one either. It also depends on your table structure. I have a table of service records, so if a vehicle came in and had its oil changed and brakes done, there would be 2 records in that table, with the date and mileage. When it comes in again for oil, another record gets added. That lets me find the last time its oil was changed, with a query, and of course keeps the history of what was done. Comparing the last service to its current mileage and the service interval (3k miles) tells you how close it is to needing an oil change. From there you can set something on a report that will flag it as due. For instance, if I have a car come in for oil and it's within a certain number of miles (set by management) for another service, that service will be listed on the report they will print out to give to the mechanic.

[shands]

Thanks for your reply Paul. I've run into another issue and haven't been able to even attack the first yet (due to IT not fixing my permission issues) -- I finally got into the server where everything is housed and I did find the forms/reports area where the admins enter the data, but I cannot access any of the queries or reports in their either. I dont understand how the reports and queries are updating the database if they are not in the same access file...?? No one seems to know anything there and the guy who created the database is who I replaced. There is no one else to ask. Geez what a great first assignment at a new job....they knew I was not a database person either. Fun, fun.

[pbaldy]

Well, the "normal" setup for an application is what's called being split. The forms, reports and queries are in one mdb/accdb, and the tables are in another. In the first one you'd see tables listed in the navigation pane with a little arrow next to them. That means they are linked, not actually in that file. Are you seeing anything like that?

[shands]

Yes actually there are arrows next to the tables. So I guess my next question is, how to the two seperate files get "linked" - how is that operation done while designing the database?

[pbaldy]

Several ways. You can build it all as one database and use the splitter wizard, you can build them separately and use the Import function to link them (you're given an option to import or link). It can be done in code, but it's less common.

[shands]

Thanks for the direction! I am feeling a bit less lost, lol. I know I will get this figured out but its a bit intimidating when you are brand new.

[pbaldy]

No problem, post back if you get stuck. Remember the old saying about big, seemingly impossible tasks: "How do you eat an elephant? One bite at a time!"