Solved

Use Access Database as Table Source for Homemade Help Desk

Posted on 2013-06-08
17
436 Views
Last Modified: 2013-06-21
This question is a continuance/spin-off from the question link below:

Previous Question

What I am trying to do is have an Access Database serve as the rowsource for drop down lists as well as validation for already existing items.  I want to have the Excel Help Desk linked to my Access Database in order to ensure the data is as current as possible.  The only reason I have to do it this way is because my users only have Excel. They do not have Access nor do they have the proficiency to work with an Oracle database.  Thank you EE for all of the help.  God bless!
TrackRequests.mdb
ADS-Parts-Help-Desk-V32.xlsm
Track-Requests.xlsm
0
Comment
Question by:Christopher Wright
  • 7
  • 5
  • 4
17 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39233647
First you need to create a query in your Access database that lists the items in the way you want them in your dropdown list.  (This is not strictly necessary, because you can specify a SQL statement from Excel, but it's easier that way.)

Next, open your Excel workbook and add a new worksheet.  In cell A1, go to Data > Get External Data > From Access.  Browse and select your database, and then select the query you created. Then click the Properties button and select the option for "Refresh data when opening the file" and, if the Access database is updated frequently, you can also choose "Refresh every N minutes".  Click OK > OK to save the connection.

Go to Formulas > Name Manager and select the range you have just added (probably named "Table_<database name>").  Click Edit... and select and copy the name. Click Cancel to close.  

Then click New... for a new name.  Enter a meaningful name (eg "PartsList") and set the scope to "Workbook".  In "Refers to" type an equals sign (=) and paste the name you have copied from the last step.  Click OK and close the Name Manager.

Now go to the cell where you want the drop-down.  Go to Data > Data Validation and choose "List" from the "Allow" drop-down.  In the source, type =PartsList (or whatever name you defined in the last step).  if you wish you can also define an inf\put message and an error alert.  Click OK to close the Data Validation dialog.

You will then have a dropdown list containing the data from your Access query.  You can do a drag fill to copy the dropdown to all the other cells where you want it to appear.

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39242050
I sorted the "Vendor" table data in Form Codes and found that there are a number of duplicate values, for example 'ZISTOS' appears twice. In this case the Vendor_ID, Buyer_Name and Buyer_ID are also the same so I could simply delete one of them but there are cases like 'Z-MEDICA' which also appears twice but where the Buyer_ID is different and I don't know which one is correct. So before I can create the Access tables I need you to clean up the data.

If you sort columns A to D you'll find that the first duplicate is 'XADS'. Fortunately that's pretty far down the list.
0
 

Author Comment

by:Christopher Wright
ID: 39245828
I have attached the updated version with duplicates removed.  I have also added the new categories that are being used now.  This is a recent change that was made while you were away.  Thanks Marty.
ADS-Parts-Help-Desk-V33.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39246151
Sorry Chris but it still looks to me like there are duplicates. Here's what I see when I open this file. I hid some rows so all the dupes would fit on the screen. The ones that are highlighted in yellow are the ones I consider duplicates (Col D is the only difference). Am I wrong?
Dupes?
0
 

Author Comment

by:Christopher Wright
ID: 39246273
Sorry about that Marty.  I did not go back and double check like I should have.  The attached file has all dupes removed.  Thanks again!
ADS-Parts-Help-Desk-V33.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39246300
Thanks. No dupes:)
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39246317
Forgive me for being confused, but I don't see what removing duplicates from a worksheet has to do with the original question, which was:
What I am trying to do is have an Access Database serve as the rowsource for drop down lists as well as validation for already existing items.
-- Graham
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39246383
This is an on-going project and I've been working with the Author on it for several months. The reason duplicates are part of the question is that there's currently a sheet in the project that contains data that we want to make into an Access table and have a unique key. The duplicates discussion started because the field that should be that key had some duplicates in the worksheet.
0
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.

 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39246448
Hi Martin

Ah!  That explains another mystery, which was how you knew each other's first names. :-)

I still don't understand what the question as stated has to do with eliminating duplicates from a worksheet.  However, if that is the object, then I suggest adding the Excel worksheet to the Access database as a linked table, and using an INSERT INTO ("make table") query with the DISTINCT predicate to select unique records for the table.

-- Graham
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39246452
Thanks for the suggestion but the Access table is going to be a replacement for the worksheet.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39246454
Precisely!  The DISTINCT query will eliminate the duplicates for you, and you will end up with an Access table containing only the unique records.
0
 

Author Comment

by:Christopher Wright
ID: 39267141
Would you agree that this question is almost answered?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39267144
Not my call of course, but IMO yes.
0
 

Author Comment

by:Christopher Wright
ID: 39267239
Thanks, I just wanted to double-check with you before I Accepted the solution
0
 

Author Closing Comment

by:Christopher Wright
ID: 39267240
As always, Marty really helped with a great deal of support.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39267248
Thanks and you're welcome.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 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

14 Experts available now in Live!

Get 1:1 Help Now