How to connect/"tag" records in a Form? (1 Viewer)

Hey guys! New to the forums but long-time reader. Casual user of Access who is trying to go pro.

I'm developing a project management database for my team at work. My department is a group of engineers that researches, qualifies, selects, and purchases vehicles for our organization's fleet. There's a lot of note taking of vehicle requirements, meeting with the end users, memos, writing a specification documents, then finally getting the project thru the byzantine requisition process.

Right now I have the foundations of the database set up, with the end goal of managing all dat thru forums - so anyone can use it. There are three key tables: Vehicles, which lists all vehicles we're replacing (with columns like type, make, model, unit number); Projects, which summarizes vehicles into groups, and adds columns like the project number, cost estimate, quantity; and lastly Contacts; which is a linked table pulling my list of contacts from Outlook (so that end users can be tied to specific projects). There's also Notes table that has a 1-to-many relationship with the Projects table.

Right now I have a basic form set up based on the Projects table. The idea is to go thru project by project, adding estimates and tracking numbers as they come available, and then add Notes on an ongoing basis (i.e. everything via the form). I want to figure out how I can create a form-based method to associate or tag vehicles to a project. Right now I have them associated manually by a column that is common to both the Vehicles table and the Projects table (1 to many relationship). However, it doesn't always work and isn't elegant. What I'm visualizing is a popup dialog box that shows the Vehicles table in datasheet form, use the filter controls to get what you need, then hit a button or checkboxes to associate them. This way it's simple enough for inexperienced people to use.

Is it possible to do this just thru native Access tools? Or am I barking down a VB script path?
Thanks so much for your suggestions and I apologize if I am asking a lot here.

Is this going to be a web-based application?

In any event I recommend you identify your tables and relationships based on the facts of your business.

That is what is it that relates these things??

Vehicles, Meetings, notes,specifications, end users, cost estimates....

Vehicles, Projects and Contacts doesn't seem to cover it all, but I don't know your business.

Whether or not Access is suitable requires more details. Who are the users? How will they interface with the proposed system?

Here's a link to more info on database planning and design.

Good luck with your project.

Hey jdraw, thanks for replying. Right now I'm only seeing this used internally as a shared file on our server. You are correct that the three tables I talked about aren't the full extent, but I didn't want to overwhelm. They are the three tables that would be involved in implementing this feature in the form (whatever method is needed to associate vehicles with projects will also be used to associate contacts with projects).

This is only meant to be a recordkeeping tool for a few people. It is not meant to be an enterprise-level high performance web-access database. Sorry if I wasn't clear about that.

Any other thoughts? I could probably post a sanitized version if y'all think it would help.

Well I mentioned web because Access Web Apps are being phased out and I didn't want you to start down bad path.
It isn't so much that these tables are sort of enough to do what you need now, it is first to get an "artist's concept of what you "project/application" entails. Don't worry about too many details; get the major subjects/entities identified along with their major attributes. Then, itemize the business rules in order to set up your relationships. Some things may just be "black boxes", but having that black box identified in a model indicates you are aware of it, you have some idea where it fits within your picture/business, and it can be recognized by others when you review the model.

If you work through 1 or 2 of the tutorials mentioned in the link I gave previously, I think it will clarify the process of getting your tables and relationships designed based on your business rules.

Too often posters try to jump into physical Access thinking that the software will do some "magic" and create a solid database. It doesn't work like that. If you can't describe it and layout your design on paper, there is no software to do it for you. You have to do the analysis and design, and from experience it is cheaper, faster and less troublesome if you get your tables and relationships identified and validated/tested/vetted with some sample data and some test scenarios first.

Good luck.

cross posted @
http://www.accessforums.net/showthread.php?t=67514

It isn't wrong to cross post. Just be courteous and tell readers that you have done so.

@jdraw, as I noted there as well, I didn't know. My apologies, no harm intended.

No problem. Happens a lot. But the forums are made up of volunteers, so preventing duplication, or working on a problem that has been solved removes some frustration and frees up time for other "things".
Good luck with your project.