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

x
?
Solved

Use Access Database as Table Source for Homemade Help Desk

Posted on 2013-06-08
17
Medium Priority
?
480 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
[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
  • 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 49

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

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 49

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 49

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
 
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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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 49

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

705 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