idk...How to create a query from two tables without a common field...

[ldybugs4me]

I have two tables in a database. One table tracks training dates; in its simplest form, the other tracks the receipt of a letter. I am tracking 5 training dates. Four of which need to be three years or younger to be valid, the other must be less than a year old to be valid. A sixth field is a “Yes” or “No” parameter, “Yes” being valid. I need all six of these fields to be valid in order to identify the individual as “eligible”. I currently have a field in the Training Table that is text based but contains “yes” or blanks (which could be altered if need be). This field tells me if the individual is “eligible” (they have completed six trainings and the trainings are current).



I want to create a query displaying all of the fields within a different table, a Letter Table, specific emphasis on whether or not the letter has been received – this field could be one of four possibilities, which could be converted to “yes/no” if need be. However, the data I want to display in the query are records that DO NOT have a letter AND/OR are not eligible. How do I do this? Dummies 101, please; I am verily new to MS Access (2007).

NOTE: There are no common fields between the two tables other than the individuals name. All names are included in the Training Table but only a handfull of the same individuals are contained in the Letter Table.

[June7]

Provide sample source data and example of desired output. Can attach files to post if you want. Follow instructions at bottom of my post.

[ldybugs4me]

Provide sample source data and example of desired output. Can attach files to post if you want. Follow instructions at bottom of my post.

Here is the basic sample data. Toon Tracking.zip

[orange]

I think you may need to restructure your database. Your Training1, Training2.... suggest another Entity.
You should research Normalization to help with designing your tables.
What are the specifications/facts that led to this database?

Training is required.....
Possible training courses....
Training Course Date is important because....
Who is the Training for?
What exactly is the purpose of a Letter?
Who writes the Letter?
Who receives the Letter?

Help us help you by telling us as much as you can about your requirement in business facts in plain English.

[June7]

Partial answer in thread https://www.accessforums.net/databas...7-a-33127.html?

Now you need to build a query that joins the two tables, include the calculation suggested in the other thread.

I agree with Orange, your data structure is not optimized.

At least need another table for ToonInfo. This table should have: ToonID, ToonLastName, ToonFirstName, PhoneNumber, Supervisor. Then only the ToonID would be saved as foreign key in the other tables.

[ldybugs4me]

Unfortunately, I am very limited as to what I can tell you do to company policies. Please note, I did not create the database, I am merely trying to make it more robust. I can restructure it in order to do so. The database that is maintained has the training class names and the field entry for each training has the date the training was taken (as reflected on a certificate that is submitted). Everyone in the database must take these trainings- all of the trainings. Only individuals that work for the organization that must take the required trainings are being tracked.

As far as the letters go, several individuals will write them. We have no desire to track that individual at this time or any foreseeable future. The letter itself is a letter of authority, several persons will receive a copy. However, for tracking purposes we are only concerned whether or not we have received it for compliancy issues. We currently annote this as simply a "yes" or blank, and other key factors named in the letter.

Ideal State: Using two tables (minimum) we want to track identified individuals and the specific date of when they took each of the 5 trainings and have filled out a required form. 4 of the trainings must be 3 years or younger. One training must be 1 year or younger. And each year the required form must be submitted. All of these parameters must be current in order for the individual to be "qualified" for a specific duty.

In addition, in another table we are tracking the receipt of the letter tied to a specific action. There can be multiple actions for one person, each requiring a separate letter, dates specific. The individuals within this table are all included in the training table, but those in the training table are not necessarily in the letter table, and may never be. We want to tie whether or not the individual has given us a copy of the action letter AND has completed all the trainings/form (trainings must be current).

Thanks so much for your time!!!

I think you may need to restructure your database. Your Training1, Training2.... suggest another Entity.
You should research Normalization to help with designing your tables.
What are the specifications/facts that led to this database?

Training is required.....
Possible training courses....
Training Course Date is important because....
Who is the Training for?
What exactly is the purpose of a Letter?
Who writes the Letter?
Who receives the Letter?

Help us help you by telling us as much as you can about your requirement in business facts in plain English.

[ldybugs4me]

June7

If I create another table as you suggested, right now Access generates the ToonID. Do I need to create it in order to have them to match? The ID number, or does it matter?

[June7]

The ToonInfo table is advised so don't have to repeat ToonName and PhoneNumber in the other tables, reduces data entry errors for misspelled names and phone numbers.

You can use the autonumber in ToonInfo as the primary key. Save this value in the other tables as foreign key.

Then build queries that link on the pk/fk fields.