Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard network printer problem ram registry router security slow software sound toshiba trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: update the column in one table based on the column of another table

Reply  
Thread Tools
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
03-Aug-2008, 05:38 PM #1
Question Solved: update the column in one table based on the column of another table
I have a database that i'm creating that has 3 different tables. Its for safety violations at work. One table is the innitial one that has all the pasic information on it. I set it up so that it automatically assigns a number to each complaint filed. The other two are for follow up. One is for how the problem is going to be fixed and who is supposed to do it, And the other one is for verification to make sure that the problem has been fixed. My problem is that i want the two follow up tables to automatically import the complaint number when a new complaint is filed. I tried to set everything up myself, but had no luck. Can someone please help? Thanks.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
04-Aug-2008, 07:38 AM #2
Hello, the trick to passing essential data between Tables is first to Set Up the Tables Relationships.
Once they have been set up you then use a Mainform for your Violations and subforms for your "Fixes" and "Follow-up", whcih are Linked to the Main form via "Master and Child" links which are your Related Fields in your Relationships.
In this way when you create or go to a main form record any data entered in to the othe 2 forms will automatically be given the correct Main form ID number.
If you want some ideas on database design I can point you to a couple of other Threads by other Posters who have learnt how to do it from simple to more advanced designs.
__________________
OBP
I do not give up easily
slurpee55's Avatar
Computer Specs
Distinguished Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
04-Aug-2008, 11:14 AM #3
OBP, other posts are made here regarding this same database - I would suggest we work on this posting
http://forums.techguy.org/business-a...able-list.html
slurpee55's Avatar
Computer Specs
Distinguished Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
04-Aug-2008, 11:14 AM #4
OBP, other posts are made here regarding this same database - I would suggest we work on this posting
http://forums.techguy.org/business-a...able-list.html
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
04-Aug-2008, 11:24 PM #5
here it is
here is my database that was requested. i'm sure that it will look a mess to anyone that is experienced at access. Any pointers and tips and tricks would be greatly appreciated.
Attached Files
File Type: rar Safety Observations1.rar (39.0 KB, 7 views)
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
04-Aug-2008, 11:29 PM #6
questions
In my other post about my forms it was asked what i was using for a record source. My record source for my form is a bunch of support tables that list places and things like that. I do not actually have a record source that is my actual database. what i want to do is pull the information i want off of the smaller tables and enter it into the main table along with some new information that is not stored on any of the tables. I guess it seems like i'm going at this all wrong.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
05-Aug-2008, 06:50 AM #7
I like most of what you have done so far, although I don't personally like Lookup fields.
The problem with your Form is that it does not use the Safety Observations Table as it's Record Source, therefore it has no where to "Save" the data to. You will notice that when viewed in Design mode all of your Fields say "Unbound" which means they aren't linked to any Table Fields.
To overcome this I have made your Safety Table the Form's Record Source and each Field "Bound" to a Field in the Table. I have added a Category field to the table to "hold" the category selected in the first Combo.
This also means that your "Save" button is no longer necessary as the data is automatically saved when you move from the record or close the form.
I do have some suggestions to make about the design of the database, you do need to set up the correct "Relationships" between the Tables.
Also I am not sure that you need a seperate "Observation Closure" table. As you can only have one observation per record and it can only be closed once I think it should be part of the Safety Observations Table.
The Observation Fixes is correct though because you could need more than one fix to allow "closure".

I will post another database with what I think the design should be for optimum use later.
Attached Files
File Type: zip Safety Observations1.zip (125.2 KB, 4 views)
__________________
OBP
I do not give up easily
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
05-Aug-2008, 10:11 AM #8
Here is the version of how I think it should all go together.
But I do think that you should also have an "Individuals" Table to select the individuals responsible for "Fixes".
Attached Files
File Type: zip Safety Observations1.zip (56.1 KB, 6 views)
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
05-Aug-2008, 05:02 PM #9
indeviduals table
yes i had thought of making an indeviduals table, but the problems that i saw was that what if someone is designated the responsible person and they are not on the list? At work there are two main people, head of maintenance and head of manufacturing. usually they will assign a person under them to take care of the problem. but there are dozens of people that that could be. I guess i could make a table and then connect it to that field but not limit entries to the list.
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
05-Aug-2008, 05:15 PM #10
How do i specify a record source for fields in a form? is that done by going to properties and then control source?
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
06-Aug-2008, 08:24 AM #11
Yes, that is how you do it, you select the Field from the drop down when you click on the Control Source.
With regard to a Combo for Individuals you can have a "Double Click" VBA routine that opens an Individuals Form to enter a new name which when closed automatically Updates both the Form that you are on and it's Combo box as well. You can see this in action on quite a few of the databases that I have collaborated on and posted on this forum.
__________________
OBP
I do not give up easily
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
06-Aug-2008, 07:14 PM #12
I tried to set a record source on my original database. I went into the form new observation and selected the area dropdown box and then clicked properties. in the data tab i selected the control source box. The dropdown list has nothing in it so i clicked the three dots instead. and i went through the tables list and found my main table and selected the column that i wanted the information to go in. But when i went back to the form and tried to use the are dropdown box it said that i couldnt edit the value because it was tied to the statement that i had just selected. What did i do wrong? what am i missing?
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
09-Aug-2008, 11:40 AM #13
Question Forms and Reports
I am trying to make a form that lists all the different collumns in my database and makes it so you can select any different number of combinations and sort the data accordingly and then view a report or a chart. Does anyone know how to do this?
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
09-Aug-2008, 01:27 PM #14
Did you get the Combo working?
What you are asking for in this question is very difficult to achieve without very advanced VBA code. Selecting the fields is not too difficult, the report is simple (the graph a bit harder) but sorting the data on any number of selected fields is difficult.
__________________
OBP
I do not give up easily
thewebbmasterone's Avatar
Computer Specs
Junior Member with 20 posts.
 
Join Date: Aug 2008
Location: New Hampshire
Experience: Intermediate
09-Aug-2008, 06:41 PM #15
Updated database
here is the updated version of my database. I added a responsible indeviduals table and made it so it would open a new form to allow me to add to it if i double clicked on the dropdown. (Thanks to you guys for the suggestion). I'm pretty happy with what i have for the data entering process. Now i'm trying to figure out a way to look up information an analize it. I still haven't figured out how to attach some of the fields to record sources. I've tried but i'm missing something somewhere. it would be greatly appreciated if someone could point me in the right direction. And as usual, any pointers or suggestions would be much appreciated.

Thanks
Attached Files
File Type: rar Copy of safety observations.rar (163.6 KB, 4 views)
Reply

Tags
2003, access, autonumber, tables

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 12:02 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.