Solved

Use Access Database as Table Source for Homemade Help Desk

Posted on 2013-06-08
17
445 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 46

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 46

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 46

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 46

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 46

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 46

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 46

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 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

22 Experts available now in Live!

Get 1:1 Help Now