Link two queries without common fields

[JGrots]

I need to link two queries together that don't have common fields. For example, I have a continuous improvement project database where I'm creating new projects to start and I have a Rejections database where if we have a rejection and determine a corrective action to fix the problem, there will be a check box on the form that says "should a project be created?" If this is checked "Yes" then a new record in the continuous improvement project database will be created and the corrective action description will automatically populate in the description field on the Continuous Improvement Project Form. I also want to be able to create projects that are independent from the Rejections database. I also need to be able to store this data somewhere in the continuous improvement project database without it updated the rejections database. Is there anyway to do this?

[June7]

Can certainly create a new record in the Continuous db (table?) but as you noted the two records will not have a relationship unless you create pk/fk fields. Where will the corrective action description come from?

[JGrots]

The corrective action description comes from the C/A field in the rejections form. What are pk/fk fields?

[JGrots]

TEST - Continual Improvement Projects.zip I've attached my DB. Hopefully this will help to solve my problem. Thanks in advance.

[orange]

I have looked at your database. My recommendation is to research Normalization and get you tables Normalized.

Get your business facts documented and create a data model to help guide your project.

see this for creating a data model . There is a sample that leads you through the steps.
http://www.rogersaccesslibrary.com/T...lationship.zip

[June7]

PK = Primary Key
FK = Foreign Key

I haven't reviewed your db but orange's advice is sound.

[JGrots]

I'm still having trouble getting this to work. I added primary and foreign keys to my tables but how do I get a new record to be created in the Continuous Improvement Project if the checkbox in the Rejections table is checked?

[June7]

As orange pointed out, this is not a normalized data structure and could give you a lot of trouble down the road.

When which checkbox is checked should there be a record in CI Table - C/A Implementation? This requires code in some event.

You haven't even built a form for the NCR Table which is where the code would have to be.


Advise no spaces or special characters/punctuation (underscore is exception) in field or object names.

[JGrots]

I wanted to keep the two databases separate. I have the NCR Table Form created, I just didn't have it in the DB I uploaded. When the "Create Project" checkbox is checked, then I want a new record to be created in the CI Table. Would the code go in the AfterUpdate on the NCR Table Form and what would it look like?

[orange]

I suggest you do whatever you have to do to get your basic concept working. Then modify specific sections with bells and whistles as you wish. But get something working that is normalized and complete. If you want to move tables to different databases (for whatever reasons) do so, after you have the basics working and you understand what you have.

We are trying to help, but it isn't effective if you have pieces in different places and haven't mentioned it.

Have you looked and worked through the tutorial I suggested?

[JGrots]

Test DB.zip I created a sample DB. I appreciate all your help/advice. If you could please look at the DB and give me some pointers on how to make this work, I would greatly appreciate it. Thank you!

[orange]

I looked at your database. you have no relationships. Relationships are the basis of referential integrity. Preventing orphan records, preventing improper deletion of records and avoiding duplication.

You should only use alphanumerics in your field and object names A-Z, a-z, 0-9 and "_" There should be no spaces and no special characters. This is an issue NCR/IPR# and is not recommended. It will cause you grief in future with syntax errors.

If you want some focused help/assistance, I suggest you read the tutorial I suggested. Not only can you work through the example that is featured in the tutorial, but you can try it with your own data. There are forms and materials to help with your own data.
Get a sample data model, or at least get a paragraph that describes what you are trying to "solve".

This is a short version of similar material 12 Steps to Better Databases
http://www.rogersaccesslibrary.com/f..._Databases.zip

[JGrots]

The relationships between the two tables is giving me trouble because it is not a one-to-many relationship. There are two places where I can get my data from: inputting directly into the CI Form or by checking the "Create Project" box on the NCR Form which will create a new record in the CI table. Therefore, the relationship between the two tables would be one-to-one because one NCRID will equal only one record in the CI table if the box is checked. Right now it is only allowing me to link the NCRID from the NCR Table to the CI Table as a One-To-Many link, one being the NCR table and the many being the CI table. Basically saying that one record from the NCR Table can have many records in the CI Table, but this is not what I want.

I will go through the two tutorials and hopefully it will help clear things up. Thanks.

[orange]

Before you get into Access and its related jargon/terminology, you should (but few do) identify what you are trying to do/solve in plain English. If you can't describe it in plain English, then I believe you will have no end of difficulty in trying to build it in whatever tool/language you use.
Most projects - planning a new jetliner, renovating a building, designing a new shopping center - all start with some points on paper, or a powerpoint slide (an old boss of mine rebuilt an entire section of IT in a government department on the basis of 10 powerpoint slides). From the few points comes a document that leads to an artist concept. Database is very much the same. If you are building based on some thing you have in your head, stop now and get a data model in place. You can take that and some sample data to the "people who are funding/supporting your effort" to get buy in. Flesh it out and get confirmation of the specifications before you jump in with "the solution".

[JGrots]

TEST - Continual Improvement Projects.zip I normalized my database and created the necessary PKs/FKs. I still need help though. In my NCR form I have a yes/no option for creating a project. If the Create Project is checked yes, then I want to create a new record in my CI Table and have the Corrective Action description from the NCR form be updated to the description field in my CI Table. Also, I want to be able to create new records in my CI Table without going through the NCR Form and I don't want the NCR Form to update when I make changes in the CI Table. I want it to be a one way update if that's possible. I would greatly appreciate anyone's help on this. Thank you!