Linked Tables (1 Viewer)

BRAD003

New member
Local time
Today, 11:48
Joined
Dec 5, 2019
Messages
4
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:48
Joined
Oct 29, 2018
Messages
21,358
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.
 

Micron

AWF VIP
Local time
Today, 12:48
Joined
Oct 20, 2018
Messages
3,476
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?
 

BRAD003

New member
Local time
Today, 11:48
Joined
Dec 5, 2019
Messages
4
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:48
Joined
Jan 20, 2009
Messages
12,849
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.
 

BRAD003

New member
Local time
Today, 11:48
Joined
Dec 5, 2019
Messages
4
@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

New member
Local time
Today, 11:48
Joined
Dec 5, 2019
Messages
4
@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.
 

Micron

AWF VIP
Local time
Today, 12:48
Joined
Oct 20, 2018
Messages
3,476
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:48
Joined
Jan 20, 2009
Messages
12,849
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.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:48
Joined
Feb 5, 2019
Messages
292
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
 

Users who are viewing this thread

Top Bottom