Solved

Cross updating records using lookup tables

Posted on 2013-01-08
9
394 Views
Last Modified: 2013-01-09
Completely new to Access, trying to build an app for management of some company assets. Currently, just a simple setup utilizing the assets template from Access. Two tables right now, one for Vans, and another for Technicians. Using lookups, (even though I have read that it's not recommended practice?) it does seem to work... almost. If I create a van record, using the technician lookup, it will create the proper van record with Van Number and Tech Number, but of course in the tech table, Van Number is still a null value. I'd like them both to be updated when creating and editing records.

I was thinking about trying to make a logic for "After Update" or maybe even within the table itself, to do a cross update?

I am also wondering if this is attacking the problem in the wrong way. Should I not be using lookups and instead, figuring out how to create queries and macros? I need to ensure there's no duplicate data, there will be vans without techs, and techs without vans, but never a tech in 2 vans, for instance.
0
Comment
Question by:J-Rodder
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 38755915
May be you can upload your DB with tables and some sample data? Cross-reference in your case seems wrong.
0
 

Author Comment

by:J-Rodder
ID: 38755972
I am using Access 2013 and Sharepoint 2013, tied to SQL2012. You would only need to look at the SQL database?
0
 
LVL 39

Expert Comment

by:als315
ID: 38756008
You can make screenshots with tables design, it will be enough.
0
 

Author Comment

by:J-Rodder
ID: 38756231
Okay, here goes. I am attaching the table links, and the query results after creating data in the following way:

Vans 10 and 20 created without entering tech number
make tech Jared Clark without entering van number
make tech Jose Miranda using existing van data (20)
make tech Mike Sellers 709 and making a new van entry before saving record.

Maybe this helps? I would like to enter data into the tech form, and have it also update the corresponding record in the van table, and vice versa.
query-results.png
table-links.png
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 39

Expert Comment

by:als315
ID: 38757715
You don't need field Van_Number in tbl_Technicians. or in table tbl_Vans.
You declare "but never a tech in 2 vans": may one van has 2 techs? If answer is - No, you should have one-to-one. If you like to have history of tech-van assignment, you may have separate table van-tech with vanID, techID, datefrom, dateto etc. To prevent duplicates - add
composite index:
http://blogs.msdn.com/b/mssqlisv/archive/2008/09/26/designing-composite-indexes.aspx
0
 

Author Comment

by:J-Rodder
ID: 38759510
Well maybe I could have been slightly more clear. I do need a Van Number field in the tbl_Van, as that's how we identify the vans. They have an actual number sticker on each vehicle, and that's how we track them. I think you are right about the one to one, and I will look into the composite index. As I feared, this is more due to my complete ignorance of how to properly structure the database. The lookup fields gave me the data I wanted in the web interface, but I was thinking that I might run a query against just the van table, and it wouldn't give me the data I want.

From what I understand at this point, a proper query would more likely involve pulling the proper columns and records from all involved tables.

It gets slightly more complicated (for me) because I will also need to have an invoice table,  connected to the vans, that will I guess be a one to many relationship? Each van will certainly have more than one invoice attached to it.

It looks like I have a lot to learn. My current problem is that most of the Access books I have found are more for the application side, and not the web databases, which are significantly different. Maybe later this year I can score some of these 2013 books on pre-order that deal with this whole Access/Sharepoint App nonsense.

Thanks!
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 38759828
I'd like to correct first sentence. It should be:
You don't need field Van_Number in tbl_Technicians or Technician_Number in table tbl_Vans.
Invoice table, of course, can, and even should have many-to-one relations with primary tables. It is correct.
Web databases are different because front side is limited in comparison with traditional Access DBs , but tables structure is absolutely same. Relational databases always have some primary tables, which are connected to tables with records (invoice is good example of such table, but every invoice also may have some common header, which could be placed in separate table and multiline detail part, where every line could have many-to-one relation to header table).
0
 

Author Closing Comment

by:J-Rodder
ID: 38759837
Thanks for the help and clarification, I am sure I will return later with other questions!
0
 
LVL 39

Expert Comment

by:als315
ID: 38760077
You are welcome
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now