Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Database - Wrong column being saved

Posted on 2013-11-26
6
Medium Priority
?
488 Views
Last Modified: 2013-12-22
Hi,
    I have two tables on my database called;

Data and Providers

Providers is made up of approx. 10 columns which are;

ID, Number, Name etc.

I have a form which has a drop down made up from the providers table, and when a record is saved, it saves the value to the Data table.... however....

On the dropdown box, it uses a SQL script to list the name, rather than the first column of ID, but when the record is saved, it saves it as the ID number, and NOT the name which is in the box on the form.

What am I doing wrong, and what do I need to do to resolve?

Thanks

Damian
0
Comment
Question by:damianb123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 2000 total points
ID: 39677411
It sounds like the ID is part of the drop-down list and that it is hidden -- in this case the width of the column is 0.  On the data tab of the drop-down, try setting the Bound Column to 2 to see if that corrects the issue.
Tom
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39677545
<<
What am I doing wrong, and what do I need to do to resolve?
>>

In the interest of keeping things normalized it actually sounds like you are doing things *right*.

In other words, you should maintain the provider name in ONE table (your providers table), and store the ID in others (such as your Data table).  You shouldn't store the name in multiple tables.  That way if the name changes (typos, etc), you only need to correct it in one place - the ID stored in any other tables is enough to look up the text name/description whenever needed.

(ie: you should leave this as is and adapt/develop your forms and reports to use the ID to lookup the name whenever needed)
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39677726
As mbizup already said, saving the ID is the correct solution to keep your tables normalized.  To see the text rather than the ID in reports, create a query that joins the two tables and select the text value from the lookup table.  You could use a combo as you do on forms but combos look awkward on reports.

For your own purposes, I know it's nice to see the descriptive value when you open the table but it doesn't take much effort to create a query that shows both.  Just be careful when you make such a query because you can update both sides of the join and you may inadvertently modify the text value thinking it applies only to the record you are looking at but in reality, it would change the value in the lookup table and so actually affect everyone.  So, in a query that joins an order to the customer table so you can see the customer name as well as his ID, if you change order.customerID to a different customer, Access will automatically populate customer.customerName with the correct customer name and the order will be reassigned to a different customer.  However, if you change customer.customerName, the change affects the customer table and that customer's name will be permanently changed where ever it is used.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 61

Expert Comment

by:mbizup
ID: 39679723
Pat,

<<  but combos look awkward on reports. >>

Which view are you referring to?  

I've found that combos are a great way to handle relationships like this on reports... they look like combos in design view (and possibly in Report View in more recent versions of Access), but they display just like textboxes in Print Preview and in the print outs -- with the text rather than the ID displayed, and without the 'drop-down arrow'.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39735237
I'm glad you got an answer to your question but unfortunately it enabled you to do the wrong thing.  Saving the ID is the correct solution.  You bring up the text value whenever you need it by using a query that joins to the lookup table.  Your accepted solution prevents the use of Referential integrity because you are saving the wrong field as the foreign key which is what mbizup and I were trying to tell you.  You need to save the ID field as the foreign key.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39735382
Agreed.  If you actually implemented that solution, you might want to revisit this question and independently read some articles / tutorials on database normalization.

As an aside, Experts-Exchange generally works best if there is more of an 'exchange' of comments... Your own participation/feedback is always a plus.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What we learned in Webroot's webinar on multi-vector protection.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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