One Combobox saving to several tables?

I am in WAAAAAY over my head!!!
I am basically automating the training department in my unit.  Here's what I 
need to accomplish:
22 people, approximately 75 courses.  Courses are devided into three phases 
of training, and can fall in 'basic' for one person, 'intermediate' for 
another, and 'advanced' for the third.  Not everyone is required all 
training, but if the course is listed, at least one of my 22 people need it.

I created a few tables.  First is Course Titles.  Not sure if this one is 
even necessary!!!  Then I created Basic Training, Intermediate Training, 
Advanced Training and Courses Completed.  All of these tables are IDENTICAL.  
(seriously...I copied, pasted, and renamed)  These tables have a column for 
'training course Title' and then a column header for each of the 22 
positions.  ALL contents of the table aside from Course Title are yes/no 
selections.  (Is it required?  Is it complete?)

I managed to make an organized form based on Course Title.  Once a title is 
selected, the form populates the data from the 4 tables (basic, intermediate, 
advanced, and complete).  It fills in all of my little checks for me.  I can 
then update or move the phases of required training based on updates and 
pushes from higher ups.

Here's my problem:  If the course title is already loaded into all 4 tables, 
I can update/save the data.  What I can't do:  Add a new course title, check 
my little check boxes, and have it populate all 4 tables with the new title.  
I get indexing errors out the wazoo that 'this course title does not exist in 
the index' bla bla bla.

So I tried going into the 'control source' for my Title field, then clicking 
the ... button, and manually adding all of my Course Title fields from all 4 
tables.  No bueno.  I think I really made it angry!!!

So...how do I make it work, or fix what I have already done to MAKE it work? 
 I debated on autonumbering IDs instead of titles so I could make it work, 
but something happened in my original Course Titles DB to make it start with 
number 2, so they would already be horribly off....

Eventually, I need to pull a report based on an individual's position that 
tells me what training they are required, what phase it's in, and if it's 
complete...  I'll deal with the reports another day...for now, I'm at a loss 
on inputting all of this stuff!!!
0
Utf
12/1/2009 3:41:01 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
343 Views

Similar Articles

[PageSpeed] 44

Ok.  So now I just feel silly.  After two days of beating my head against a 
wall, I deleted all of the records out of my tables.  Then I changed Course 
Title to Course Title ID and linked em all.  I recreated the form, including 
all of the course ID column headers...then I made them invisible and voila!  
It allows me to save it, autonumbers the basic, intermediate, and advanced 
tables, and does what I need it to do.
Now to create the reports....  *sigh*
0
Utf
12/1/2009 5:10:01 PM
=?Utf-8?B?S2ltYmVybHkzNjI2?=
<Kimberly3626@discussions.microsoft.com> wrote in
news:FF972A69-2308-487D-BF8A-9A44DA679062@microsoft.com: 

> I am in WAAAAAY over my head!!!
> I am basically automating the training department in my unit. 
> Here's what I need to accomplish:
> 22 people, approximately 75 courses.  Courses are devided into
> three phases of training, and can fall in 'basic' for one person,
> 'intermediate' for another, and 'advanced' for the third.  Not
> everyone is required all training, but if the course is listed, at
> least one of my 22 people need it. 
> 
> I created a few tables.  First is Course Titles.  Not sure if this
> one is even necessary!!!  Then I created Basic Training,
> Intermediate Training, Advanced Training and Courses Completed. 
> All of these tables are IDENTICAL.  (seriously...I copied, pasted,
> and renamed)  These tables have a column for 'training course
> Title' and then a column header for each of the 22 positions.  ALL
> contents of the table aside from Course Title are yes/no 
> selections.  (Is it required?  Is it complete?) 

You need to stop right here, and redo your tables.

You need the Course Titles.table. Add a field call courseID and 
possibly other fields, such as CourseInstructor, CourseFrequency, 
PassingGrade, etc; all things related to the course itself.

You also need a table for People. Name, Rank, Serial_number, 
DateJoinedUnit, DateLeftUnit, that sort of thing.

Another little table is needed for training Level
Fields TrainingLevelID and TrainingLevelTitle.

Now comes the big change, Destroy your THREE IDENTICAL tables, cuz 
that'sa a big no-no in database design.

You want 1 table, with the following columns:
CourseId
People.SerialNumber
TrainingLevelID (just the Number,1,2 or 3)
DateTaken
Pass (or grade)(your choice)

Once you have that, you can set up queries to return the data in the 
way you want, and other ways too, which you will never be able to 
make reports with your existing structure.


>  I debated on autonumbering IDs instead of titles so I could make
>  it work, 
> but something happened in my original Course Titles DB to make it
> start with number 2, so they would already be horribly off....

Why, an ID number does not need to start with 1, nor does it need to 
be continuous. Even if it's totally random, it still works.


-- 
Bob Quintal

PA is y I've altered my email address.
0
Bob
12/1/2009 11:07:58 PM
Reply:

Similar Artilces:

I have published a worksheet to the web and it works good but one problem please help
Hello I published a worksheet to the web. The address is http://www.inforamp.com/~kidd/html/pmcshop1.htm The problem is I want to get rid of the scroll bars and the top men bar so ONLY the worksheet is present. Can someone tell me what I did wrong You help is greatly appricated Pau -- kid ----------------------------------------------------------------------- kidd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1579 View this thread: http://www.excelforum.com/showthread.php?threadid=27378 ...

cannot save to a Netware volume in Publisher 2002
I have encountered a problem when trying to save a document in Publisher 2002 to a Novell Netware drive. If the folder name is longer than 8 characters, Publisher will report an error, "Invalid file name". The file can be saved to local drive. If the network folder name is shorter than 9 characters, there is no problem. This problem does not exist for word 2002 or excel 2002 when saving to the netware volume. The version of netware is 4.11 sp9 and long file names has been enabled on all the volumes. Several netware clients have been used with no success. As the problem is not with Wo...

Pivot Table Data Fields Won't Total
Hello, I'm trying to create a chart to display 3 categories (ATM,VRU,WWW). I get the Data from a SQL script which I then import into an Excel spreadsheet. One of the columns is numbers and I format the column as "Number" with two decimal places. When I create the Pivot Table only one of the criteria fields (ATM)will give me a total. The other two fields keep amounting to zero. I've even tried to skip the Pivot Chart and just insert Sub-Totals into the spreadsheet choosing to insert the total after each change in criteria type; but still only one type of criteria will ...

Quickly Format Multiple Data Series in One chart
I do a lot of charts that contain spectral data. I'll have 20-30 data series and I plot them as a XY scatter using the line only format. However the default thickness in XL 2007 is way too fat and consequently I loose any ability to differentiate the series. So far the only way I've been able to change this is by actually selecting each individual series and changing the thickness. Is there a way to change the default line thickness to 1/2 pt? Or at least to change all the series in a chart in one go? In Excel 2007, if all you are changing is one single attribute of a serie...

Automate saving of attachments on new incoming emails
Hi all, I'm new to developing for Office and Outlook (I come from a Lotus Notes developing environment). I have some generic email inboxes that different clients would use to send their attachments to. What I need is to find a way to write a script/macro that would perform saving of the attachments on all new incoming emails. I found a sample code that I could use to get what I want: http://msdn.microsoft.com/en-us/library/ms268754.aspx My question is... Can this code run on the server side for the generic emails (there are 6 of them) that I want it on, or do all 6...

Saving an entire workbook as a PDF using VBSCript
Hello all! I'm using a VBScript file which I've added to Server 2003 scheduler to automatically open up Excel, load it with a workbook which is in turn populated with data, print it out, dynamically come up with a filename which is figured off of the current date and time which is then appended to the original filename of the workbook, save it in HTML format, then close itself. Works like a charm. I've decided I don't want to save the file in HTML format but would instead like to save it in PDF. The reason being is HTML files can modified by any with a text editor. ...

How to move e-mail from one PC to another in Outlook and maintain.
I would like to know how to move e-mail from one PC to another PC and maintain the integrity of the e-mail. Forwarding each e-mail is not pratical in this case. You need to post more information. Since this is an Outlook discussion group, I'll assume you are using Outlook. Is the email in a .PST file, or on an Exchange server? If a .PST file, just copy the .PST file to the new computer and then tell Outlook on the new computer where it is. If it's on an Exchange server, then you don't need to move anything. "rwrhine@psa" wrote: > I would like to know how to m...

Transfer a calculated value from one report to another
I had 2 reports that use different row format and different column format eg: repor1 is using row1 and col1, where as report2 is using row2 and col2. How can I transfer a partiulcar calculated result from report1 to report2 as report2 uses that value in some calculation. thks If this is in Report Writer then I would say No but if its in Crystal Reports then you can copy the formula. -- http://ddelprado.blogspot.com "HFLo" wrote: > I had 2 reports that use different row format and different column format > eg: repor1 is using row1 and col1, where as report2 is using row...

Limiting to one number
I have few fields in excel where I should input numeric data (i.e. days of the week - from 1 to 7) Anybody know how can I limit cell to allowing input on juts one number to prevent wrong input? Limiting decimal places is easy but limiting main numeric field...? Highlight the cell and click on Data->Validation. Select "whole number" in the first box. Select "Between" in the second. And input 1 and 7 as the minimun and maximum. "swen@post.com" wrote: > I have few fields in excel where I should input numeric data (i.e. > days of the week - from 1 to ...

PIVOT TABLE FORMULA #2
I have a pivot table with lot s of companies name. Each company has only one country associate. I need to do a calculated item formula but when I do this something happened because now each company is being associate to all countries and this is not correc. Is there any way fix this ...

one line of a label
I have created a label that repeats 14 times on one A4 sheet with 5 lines of info but i want one line to be different on each label how canI do this easily any info would be great Create a data base with this particular line. Fill in the label with the four repeating lines add the fifth line as a mail merge object. Help here: Mail, e-mail, and catalog merge http://office.microsoft.com/en-us/publisher/CH100502901033.aspx -- Mary Sauer http://msauer.mvps.org/ "Chris" <Chris@discussions.microsoft.com> wrote in message news:CA61D003-1661-48F9-BB6E-9A9A7B662...

hiding certain columns in pivot table
Hello, I have a pivot table that I have inserted a calculated column which appears for every column. I'm just wondering if there's away to hide particular calculated columns in a pivot table. I'm only interested in the calculated figure for one particular column and what to hide the figure for the rest of the columns. Thanks, Alan ...

Saving all messages in Exchange server 2003
I have a client who is very paranoid. He wants me to save all messages that come through exchange, so that even if a user deletes his/her message, there is a copy for the owner to pull up. Is there a way to do this in Exchange 2003? Thanks in Advance. Check this out: http://www.msexchange.org/tutorials/Implementing-Exchange-2003-Message-Journaling.html "DP" <DP@discussions.microsoft.com> wrote in message news:58D6020E-5D12-4BC9-8360-984EC35876EF@microsoft.com... >I have a client who is very paranoid. He wants me to save all messages that > come through exchange, ...

Table Open Error
I am running Great Plains Ver 6.0. I installed a new client but when they print a report from an open table, they get "An open operator on table GL_Account_MSTR has an incorrect number of keys" Then I get a second window that says "Table Open Error". Other users work fine. I tried copying the Dynamics.set file, but the same error. Any suggestions would be appreciated. Thank you Rich It sounds to me like your Dynamics.dic dictionary has not been properly synchronised with your account framework and additional sorts. You can change the Synchronize=FALSE to Synchroni...

Table no longer copies formulas to new rows
It's 32-bit Excel 2010. I have a table of date, deposit, withdrawal, balance, and some other columns. Previously if I entered a date in the next row, the formulas automatically propagated themselves to hat new row; now hey don't. I don't know what I did to cause that change, but I'd be grateful for guidance. I tried resizing the able to contain all the existing rows, but that didn't help: when I add a new row the formulas still don't copy from the row above. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA h...

Looking for a loan calc spreadsheet, one that if you make an extra payment, it will calculate into the duration
Anyone have one of these? I would like one to use with my mortgage and my car loan. I always pay a little extra each month, so I would like to now what these payments will do in the long run. THanks in advance. Make a simple loan amortization table. The following assumes that you are not making extra payments, just paying extra on regularly scheduled payments - Put the loan amount in C1 - Put the annual interest rate in D1 - In D2 divide D1 by the number of payments per year (=D1/12 for monthly payments) - In A2 put =ROW()-1, as a payment counter (or some other payment ID that might ...

how to pull a record to a form and save it to another table
hi, I want to pull data from more then one field ( as it seems with a combo box with multiple fields listed in a row ) and have it save these fields to a diffrent table. What I'm trying to do is take a Whole record and save that record to a new table. Thanks L Why? It's seldom a good idea to store redundant copies of data. You can use an INSERT INTO query: Dim strSQL As String strSQL = "INSERT INTO TheOtherTable(Field1, Field2, Field3) " & _ "VALUES(" & Me.txtNumericField & ", """ & _ Me.txtTextField...

can not open linked table
When trying to open a linked table I get an error message that the file is opened exclusivelyby another user. I have used this link before many times and have knowingly done nothing to change it. I am using acces 2007 on a vista lap top to an access 2000 database on a different computer (xp) which uses access 2000 . If you are certain that you are the only user, close Access and delete the LDB file on the remote computer. If there is more than 1 user, 1 or more have the database opened exclusively. Go to Options on each computer using the database, and change that. "jnacces...

invalid reference error saving 2003 file in 2007
I have a large workbook (50+ worksheets) that originated in Excel 2003. I would like to save it as a 2007 workbook so I can get larger worksheets. When I try to save it as 2007 file, I get an error message "A formula in this worksheet contains one or more invalid references." Is there any way to save this in Excel 2007 format without actually finding and correcting the invalid references. I'm sure that there are many invalid references in the many spreadsheets and I don't have to time to find every one. I have not had any problems with the Excel 2003 format. Th...

using data tables to create XML
So I created my dataset class. Then added my datatables and the columns to the data tables. What I want to do is when I fill the datatable have one of the columns be another datatable so that I can create my complex type when I get the XML. So lets say I have two tables Stop_Location and Stop_Address Table Stop_Location Column Stop_Name Column Stop_City Column Stop_Address Table Stop_Address Column Stop_Address1 Column Stop_Address2 Then when I fill table Stop_Location I would like Stop_Address to actually be the columns from table Stop_Address. I tried to mak...

Losing conditional formatting when saving
I have created quite an extensive excel file with a significant amount of conditional formatting. I have reached the point where if I save now the conditional formatting of the last few rows is not saved. Is there a way around this or is there a limit to how much conditional formatting you can use? I've never noticed xl dropping any CF, but I don't use it a whole bunch. But if it drops it for you, maybe you could reapply it in the workbook_open/auto_open code each time the workbook opens. You may want to post some specifics. How many rows. How you used Format|conditional forma...

Auto-save of e-mail does not work with Word as mail editor!
I am using Office Microsoft Professional 2003, with Windows XP Pro with SP2. I use Outlook as my main e-mail program, and have "use Word as e-mail editor" selected. I have "auto-save" chosen in preferences, so while writing a longer e-mail, at specified time intervals the message will auto save, so that if my computer crashes while writing, I will not have lost what I write. However, while writing an e-mail that takes longer than the specified time interval for auto-save, a box keeps popping up that says (with "Microsoft Word" in the heading): "Savin...

How to tackle categories for transactions that are transfer from one account to another
I have setup money to download data from my bank checking and credit card account. Every month I pay the credit card bills through my bank checking account. This shows up as a withdrawal from my bank account and deposit into the credit card account. However, I don't know what category to assign it to. The problem is that a withdrawal is usually categorized as expense and credit as income. Thus, money artificially inflates my income and expense by the same amount in reports. At present I created a category called transfer: bank account to credit card and configured money to ignore this cate...

Need to transfer mail from one outlook to another...
Hi folks, My gf and I have two computers, both with Outlook (and Win XP). Her computer has what appears to be a bad motherboard so it will need to be replaced. We have one on order but she desperately needs some email that is in her Outlook program. my question is this: If I take her harddrive out and slave it to my system is there a way I can pull her email into my Outlook program? This may or may not be possible but any help would be greatly appreciated. Thanks, Jeff Jones Austin, Texas Sue Mosher's Slipstick site has a great writeup on what files to copy over and what they do...

adding up several cells
Hi - thanks for your responses regarding my problem. I managed to get it working sort of using McGimpsey & Associates and the Worksheet Function Accumulator (using Circular References) - I used this because I have loads of cells to apply this to - I had to copy this down over 30 cells. It works for the first cell but does weird things when entering data over the cells - it isn't stable. I need a solution the will work over many cells - other solutions seem to refer to single cells - any help would be very much appreciated!!! thanks again First of all, you shouldn't have ...