Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-05-2019, 02:59 PM   #1
BRAD003
Newly Registered User
 
Join Date: Dec 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
BRAD003 is on a distinguished road
Question Linked Tables

Hello everyone,

I have been searching the web for a solution to this without and success and I am hoping someone on this site can help.

I am working on importing information from an Excel workbook into 15 different tables in an existing Access database. What I have is a parts library in Access that feeds into our design tool and I am trying to incorporate inventory information from our ERP (inventory Management) system into the design tool through our Access database to be able to report the current inventory along with the part data.

I have been able to link the excel workbook exported from our ERP as a linked Table in Access but I cannot figure out how to lookup the corresponding inventory information in the linked Table and have it reported in each of the 15 tables. I am basically trying to do the equivalent of an Excel Vlook in access so only 1 file needs to be updated on a regular basis.

I understand I can create a query to do this easily but I need the information linked to a table to correctly import to our design tool. I also looked into have a query generate new tables each time but all sorts of formatting issues and seemed like a clumsy way to transfer information from one table to another.

Hopefully this has come up before, maybe someone can point me in the right direction. Unfortunately I couldnít find any threads addressing this. Any assistance is greatly appreciated.

Best Regards,
Brad

BRAD003 is offline   Reply With Quote
Old 12-05-2019, 04:02 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,294
Thanks: 66
Thanked 1,685 Times in 1,640 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked Tables

Hi Brad. Welcome to AWF!

The equivalent of VLookup() in Access is DLookup(), but I am not sure if it's really what you need.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
BRAD003 (12-05-2019)
Old 12-05-2019, 04:08 PM   #3
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,524
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Linked Tables

Quote:
lookup the corresponding inventory information in the linked Table and have it reported in each of the 15 tables.
That sounds wrong - storing repeated related data. I have to wonder why there are 15 tables for the same thing. Or did I misunderstand that?

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 12-05-2019, 04:36 PM   #4
BRAD003
Newly Registered User
 
Join Date: Dec 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
BRAD003 is on a distinguished road
Re: Linked Tables

Sorry the different tables are for the different part types and each table has a different set of fields. The part number and inventory column would be the same fields in all the different tables.
BRAD003 is offline   Reply With Quote
Old 12-05-2019, 06:12 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,771
Thanks: 118
Thanked 1,546 Times in 1,451 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Linked Tables

Quote:
Originally Posted by BRAD003 View Post
Sorry the different tables are for the different part types and each table has a different set of fields. The part number and inventory column would be the same fields in all the different tables.
Adding more parts with different attributes would then require more tables to be added. Not a good solution.

Consider an EAV (Entity-Attribute-Value) based structure to overcome this. There have been extensive discussions on this forum during the past year.
Galaxiom is online now   Reply With Quote
Old 12-05-2019, 07:33 PM   #6
BRAD003
Newly Registered User
 
Join Date: Dec 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
BRAD003 is on a distinguished road
Re: Linked Tables

@thedbguy
You are right dlookup is not really what I need because I cant use this funtion to lookup data in one table and copy it to another table. From what I can tell Dlookup is only useful for looking up data in tables and reporting to a form or report which doesn't work for my application.
BRAD003 is offline   Reply With Quote
Old 12-05-2019, 07:48 PM   #7
BRAD003
Newly Registered User
 
Join Date: Dec 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
BRAD003 is on a distinguished road
Re: Linked Tables

@Galaxiom

I was thinking there was a simple function out there that I was missing, but I guess not. I am afraid an EAV setup is way over my head. I am thinking I may be better off trying to modify our design tool to allow import data from a query instead of a table, assuming that is even possible.

Thanks for your help.

BRAD003 is offline   Reply With Quote
Old 12-05-2019, 09:05 PM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,524
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Linked Tables

Quote:
From what I can tell Dlookup is only useful for looking up data in tables and reporting to a form or report
Not true. The function returns a variant but what you do with it is up to you. There is no reason why the returned value could not be used to update or append to a table field, even in a query.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 12-06-2019, 12:55 AM   #9
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,771
Thanks: 118
Thanked 1,546 Times in 1,451 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Linked Tables

Have you checked if you can connect directly to the back end of your ERP and query the data directly from its tables without going through the export to Excel?

With any luck that data is normalized too.
Galaxiom is online now   Reply With Quote
Old 12-06-2019, 04:52 AM   #10
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 50
Thanks: 11
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Linked Tables

Quote:
Originally Posted by Galaxiom View Post
Have you checked if you can connect directly to the back end of your ERP and query the data directly from its tables without going through the export to Excel?

With any luck that data is normalized too.
This is what I do with my database to get stock data from Sage Accounts. Much easier than export from one then import into another. What software do you use for your ERP?

~Matt

MattBaldry is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Tables and Creating Reports based on those linked tables rexmorgan Tables 1 01-30-2011 08:03 PM
Linked Tables, Local Tables and ODBC lcbateman3 Tables 2 01-23-2009 04:40 AM
Number of Sub Tables linked to Main Tables chris11590 Tables 16 09-04-2008 06:22 AM
Changing Linked Tables into Regular Tables rheide General 3 12-27-2007 08:57 AM
Linked Tables to Local Tables Problem jennilewis Tables 0 08-26-2005 01:47 AM




All times are GMT -8. The time now is 07:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World