joining tables 05-02-07

Hi.  I'm confused and just can't seem to grasp how a 3rd table would result
from:

Table 1 = Employees and employee info
Table 2 = Procedures and dates completed(there are 500 different procedures
that any employee can be trained on at anytime....there is no schedule for
these)

I have a 3rd table I have created which has Employee ID (from table 1) and
Procedure ID (from table 2).  I just can't understand what to do with it.  I
need to create a form where I can click on the employee name at the top and
then be able to fill in the date(s) when a procedure has been completed.
And another form where I can click on the Procedure and have it show me which
employees have been trained.  I wanted to do it all in one table, which is
too much data.  That would make it much easier for me to understand.

Can someone clarify what I need to do?  My mind is going in circles here
trying to put it all together!
Thanks,
Lori

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1

0
Lori2836
5/2/2007 7:05:46 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
780 Views

Similar Articles

[PageSpeed] 50

On Wed, 02 May 2007 19:05:46 GMT, "Lori2836 via AccessMonster.com"
<u23986@uwe> wrote:

>Hi.  I'm confused and just can't seem to grasp how a 3rd table would result
>from:

You've got it backwards. The third table doesn't "result from" the
relationship. It is how you IMPLEMENT the relationship.

>Table 1 = Employees and employee info
>Table 2 = Procedures and dates completed(there are 500 different procedures
>that any employee can be trained on at anytime....there is no schedule for
>these)
>
>I have a 3rd table I have created which has Employee ID (from table 1) and
>Procedure ID (from table 2).  I just can't understand what to do with it.  I
>need to create a form where I can click on the employee name at the top and
>then be able to fill in the date(s) when a procedure has been completed.
>And another form where I can click on the Procedure and have it show me which
>employees have been trained.  I wanted to do it all in one table, which is
>too much data.  That would make it much easier for me to understand.

Table3 should start out empty - nobody has had any procedures entered yet.
Note that the DateCompleted should be in Table3, not in Table2 - a Procedure
doesn't have a "date completed" by itself, only with regard to a particular
employee completing that particular procedure! Different employees will (I
presume) complete the same procedure on different dates.

Create a Form based on the Employee table (Table1).

Create a Subform on this form based on Table3.  Use EmployeeID as the
Master/Child Link Field. Put a Combo Box on the subform bound to
Table3.ProcedureID.

You can find an employee on the mainform (you can put an unbound combo box to
locate an employee quickly if you wish). On the Subform you can then select
which Procedure that employee has taken (and enter any other data such as date
trained, outcome, etc). on the "new row" of the subform. If that employee has
taken additional training, use the combo box to select a procedure on a second
row.


             John W. Vinson [MVP]
0
John
5/2/2007 8:47:47 PM
Reply:

Similar Artilces:

after running query table shows id number not name how to fix
I have a table that is in the correct format. When I run a append query against this table the format is off, there is a number where there should be text. How do I fix this. If both fields exist, the ID and the 'formatted name', and you see the id while you expected to see the formatted name, you may have lost the 'auto-lookup' feature, in the TOOL you are using. Check ELSEWHERE to see if the auto-lookup still works there. If so, well, use the 'view' showing the translated id by its formatted name, or use a query that does the lookup (with a JOIN, or something...

Additional column formula in pivot table
I am a relative newbie to pivot tables. Using Excel 2003 I have, for example, 8 columns of quarterly financial data. I would like to add a 9th column to calculate the CAGR for the 8 quarters. Can that be done within the pivot table? ...

Doing joIn with REGEXP? (MySQL)
Hi Im working with MySQL. I have similar data in 2 different schemas Im trying to relate. This particular example relates to US states being stored differently in both systems. So, say I have the 2 tables as follows: STATE_1 ----------------- | ID | STATE | ----------------- | 1 | AL | | 2 | AK | | 3 | AS | ................. STATE_2 ------------ | STATE | ------------ | US-AL | | US-AS | | US-AK | ............ I want to get the STATE_1.ID value where the STATE fields are equal. Note that STATE_2.STATE values are the same as those in ...

Comparing Date Ranges in one Table to Date Ranges in another Table
I am trying to compare a sheet with claims (with from and through dates) to another sheet with amounts applicable to a different and potentially overlapping set of date ranges. In Sheet 1, each record consists of a claim ID, a member ID, and the from and thru dates. In Sheet 2, there is a Member ID, from and thru dates (more than one set of dates per member), and an applicable dollar amount associated with that date range. What I am trying to do is to show the dollar amount in Sheet1 for each claim that matches with the date ranges shown in Sheet 2. Although my ultimat...

Conditional Formatting 06-22-07
Hello, I have been having some difficulty with some conditional formatting that I have been applying to a form that I am working on. I have built a database at a manufacturing facility which stores quality testing results and allows people to retrieve those results easily. I am trying to set up the forms where the operators enter their results to have conditional formatting so it turns red if the measurement is out of spec. On the surface this sounds easy. However, we run over 20 different products each of which have different specs. I have a subroutine that is called Enable that I run f...

Sales Literature 07-22-04
Hi, Is there any way to email sales literature to a contact or lead. It seems that documents can be attached but sales literature cannot. Am i missing something. Thanks Barry There is a solution from a company in germany which allows to attach sales literature to e-mail messages: http://www.awisto.de/awisto/content/products/sales_literature.aspx Markus MattNC <MattNC@discussions.microsoft.com> wrote in message news:<C0330DA5-587B-420A-9FDB-E6D71939A1DC@microsoft.com>... > This feature is not yet available in v1.2. The Sales Literature section is basically just a reposito...

better to filter on join or where clause
If you have something in your where clause that could be put in the inner join would it be better there? SELECT * FROM tableA a JOIN tableB b on b.key = a.key Where b.status <> 15 and b.status <> 20 or SELECT * FROM tableA a JOIN tableB b on b.key = a.key and b.status not in (15,20) Any difference? Thanks, Tom There is no difference when using inner join. The optimizer will generate the same execution plan. It is more a preference/style of writing queries. I prefer to place filters in WHERE and leave only join predicates in the ON clause: SELECT ...

Filtering table info
Hi, I have a table of which lists 50 employees and their skills. Cell codes ( D = Day, 24 = 24 hr cover etc etc etc) are used to show if they are at work and if so what type of shift they are on. The table logs a months work rotas at a time and can be very difficult to follow. Out of the 50 names only 10 will be at the location I'm interested in on any given day. I would like to filter out all of the employees who do not meet any of around 5 different criteria. I have looked at autofilter but you can only include 2 criteria per column. Hope you can help Thanks in anticipation ...

Producing combined table grouped by categories
Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 ...

Table of content
Iwould like to know what means "Word did not find any entries for your table of contents. In your document, select the words to include in the table of contents, and then in the Formatting Palette under Styles, click a heading style. Repeat for each heading that you want to include, and then insert the table of contents in your document. You can also create a table of contents by clicking the Create with Manual Formatting option and then type the entries manually." I tried to do all that guided above. Unfortunately no results. Help me to use table on content, please. ...

Append 2 tables with variable names
Hi there... Here's my problem. I have 2 tables in an access 2000 database. The table names are unknown...they could have any name. Through VBA how can I append one table to the other? Hi, you can run append query: currentdb.execute "Insert into " & strTable1 & " (Field1, Field2) Select Field1, Field2 From " & strTable2, dbfailonerror Where strTable1 and strTable2 variables hold tables names -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "ambushsinger" <ambushsinger@dis...

Laptop power problem 05-15-10
I recently put on my laptop but the screen is dull like the power is low but when i use the power supply it stays dull but it says its charging, but the percentage never rises. When i take out the power lead the screen brightens up like it is fully charged, but i get a message saying to use the power supply as the battery is low. I do this then the screen is dull again. Any one any ideas. p/s iused a different power supply but still the same problem. dessie wrote: > I recently put on my laptop but the screen is dull like the power > is low but when i use the power supp...

Pivot table hide zeros?
I am running a pivot table and some data is showing blank or zero's. Is there a way to not show the records that have zeros? So when I run the sum function of the data, only data greater than zero is displayed in the final table? Thanks a lot. ...

joining domain
hi! Is it possible to have the users rejoin xp,win7 to win2003 AD with the computer object already exist in the AD? i know that this can be done through administrator group, but can this be done by the user without adding them into the domain admain grp or accoutn operator? what's the best practice and previlege that i shd give to the support team who only need to able to join the pc into the domain? Thanks. They have to be able to delete and add. If you are talking about doing this to one machine not a real big deal but if you want folks to do this all the time it c...

Lock a Table
Hi All, Is there a convenient way to programatically allow the current user to have full access to a table in the current db, while preventing other users from accessing it until the program releases it? -- Mac C. Thanks Larry, I should probably be more specific. The table is used for temp storage of records that will be accessed by another application (MsWord via merge mailing). A form in the database is bound to the table. When the form opens it invokes a public procedure that deletes all records in the table and rebuilds it from scratch. After that proc returns, the form requi...

Report Question 03-26-07
Can anyone explain to me if it is possible to take a text field in a report to make it simulate columns without changing the page setup options. Any help is greatly appreciated. Thanks! Kbrad28 -- Message posted via http://www.accessmonster.com kbrad28 via AccessMonster.com wrote: >Can anyone explain to me if it is possible to take a text field in a report >to make it simulate columns without changing the page setup options. Any help >is greatly appreciated. Thanks! Normally this kind of thing is done by using a subreport for the details. This entails creating a main report q...

Cell Format 05-25-10
I want to enter <shift>: in a cell (this would be Shift Key and the colon character to enter a date). And then automatically format this cell so that the date is on one line, and the value "Critical Date" on the second line. So the cell value would look like this: 05/25/2010 Critical Date Can someone explain how to do this? If it can be done with code, I would be interested in that also... First, ctrl-: (control-colon) will enter the time, not the date. You want ctrl-; (control-semicolon) to enter the date. Second, you can use a custom number format to...

Pivot Tables #11
I have a userform, and it has 2 textboxes, is there a way that I can write a macro that will only display that data on a pivot table between the 2 dates entered in these textboxes? Right now I have the below, but I don't want to have to have all the dates written like this. With thisworkbook.sheets("CARS Survey Results by Date").PivotTables("PivotTable2").PivotFields("Date") ..PivotItems("9/28/2005").Visible = True ..PivotItems("9/29/2005").Visible = True ..PivotItems("9/30/2005").Visible = True End With I would like to ente...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only the elements that have a non zero value while also display...

"Pivot Table" option in Add / Remove buttons for Pivot Table Toolbar
Hello: I need to hide / disable / inactivate the "Pivot Table" button in the Pivot Table Toolbar so that a user can not select it (make it available). The users will have access to the Pivot Table Toolbar so that they can refresh data, select fields, collapse and expand data etc. but not have access to the Pivot Table button which gives access to (Pivot Table Wizard, Options etc.) Please help. Thanks. Forge On Aug 22, 3:02=A0am, Forge <forg...@gmail.com> wrote: > Hello: > > I need to hide / disable / inactivate the "Pivot Table" button in the > Pivot...

summary table from various sheets
I have several sheets detailing prices of widgets. Each sheet is for a different material Rows show width of widget, columns show height Trying to make a simple quote sheet where I can enter the height and width so that the sheet shows prices for that size in each material. Tried LOOKUP, Pivot, address, indirect and 3d reference but can't get any to do this. Thought it would be easy! Any advice welcome (using Excel 2003 have Access 2000 if needed) Thanks Bruce, It would be easy, but you've put the info in separate sheets. See http://www.smokeylake.com/excel/excel_truths.htm. S...

delete rows 05-13-10
Hello, My simple program would make me believe that all rows 1 to 16 will be deleted. Not so. Only the odd numbered entries in col.A got deleted, entries 2,4,6,....16 remained. My simple program is this: ------------------------------------------------------------------------------ Sub deleterows() Dim i As Long For i = 1 To 16 Rows(i).Delete Next i End Sub -------------------------------------------------------------------------------- Thanks for your help. Regards, Gabor Sebo Hi Gabor, When yor delete rows in this order the row counter gets mixed up. ...

Joining worksheets / outer join
I've found some spreadsheet joining discussion threads, but I haven't found one which I think is applicable... Basically, I have 5 worksheets in an Excel file to be used as a mail merge. Each worksheet has lists of companies and the industries that they handle. Some companies have offices at different addresses, which should remain as seperate rows. Some companies handle more than one industry and are thus in more than one worksheet. Some of the companies have one business contact for all industries, some have seperate business contacts. I want one worksheet, which merges data from...

Query Join
Hello, I need your assistance as I am a beginner with MS Access 2007. Please review the SQL and hope it makes sense. I am trying to capture all records from 2010 AOP even if there is none from SAP GL side. It seems to work with SAP GL, but not all of the AOP side. What am I missing? Not sure how to fix. SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS SumOfP4, [2010 AOPII with Benefits].P4 FROM [2...

Tables in Publisher #2
I produce a 84 page A5 sized handbook for a sports league. I put a copy of last years league tables in the handbook. There are 8 seperate leagues over two pages. Whilst the tables are small, there is space to make the tables bigger, but I cannot seem to achieve this by the obviuos methods. Also I'd like to reduce the size between the columns in the tables - but again fail to do this. It's the second year of the handbook and I'm sure that I typed the tables from scratch last year. Any idea's?? -- Thanks Squashed Tom What happens when you try to enlarge the table? Can you se...