Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 09:13 AM   #1
templeowls
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
templeowls is on a distinguished road
Create a query from two tables that autofills column

I have retail stores with a database that tracks events from the locations.

I have two tables:
Table 1: houses all the events and the data associated (location, date, description, etc.)
Table 2: houses each location with the corresponding manager

I'd like to create a query that links the two tables. Basically it'd have all the same columns from Table 1, plus a managers column based on the info in Table 2. Hypothetically, the correct manager would populate based on the location. I just don't know how to actually link this.

Doing this would save me precious time, as there's thousands of entries in Table 1 and it'd take forever to manually add the manager to each entry.

Please help. Thanks

templeowls is offline   Reply With Quote
Old 06-11-2019, 09:42 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,555
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Create a query from two tables that autofills column

Hi. Welcome to the forum. I hope you're not trying to store/copy the manager info to the location table. If you're only trying to produce a combined data for a report or a mailing campaign, you can use a query to do that. For example, in the query designer, you can add both tables and then link them using the LocationID (or whatever is the primary key for the location table). You can then drag all the fields from Table 1 and only the Manager data from Table 2 on to the design grid. When you run the query, it should show all the assigned managers for each location.
__________________
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:
templeowls (06-13-2019)
Old 06-11-2019, 10:14 AM   #3
templeowls
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
templeowls is on a distinguished road
Re: Create a query from two tables that autofills column

Link the LocationID from Table 2 to what? This database is likely not perfectly set up to adhere to Access' standards, but has always performed what we needed up to now. I don't want to redo the entire database, just so I can have the manager's name on reports. I'll likely just forget it tbh.

Table 1 has the following columns: ID, Date, Event Type, Employee Name, Location, Description

Table 2 has the following columns: ID, Location, Manager

templeowls is offline   Reply With Quote
Old 06-11-2019, 10:26 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,255
Thanks: 105
Thanked 2,487 Times in 2,283 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Create a query from two tables that autofills column

Link the location fields from both tables in your query
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
Old 06-12-2019, 03:32 AM   #5
templeowls
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
templeowls is on a distinguished road
Re: Create a query from two tables that autofills column

When I do that, I get the error "Cannot join on Memo, OLE, or Hyperlink ([Events].[Store Locations]=[Managers].[Store Locations])"
templeowls is offline   Reply With Quote
Old 06-12-2019, 06:49 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,255
Thanks: 105
Thanked 2,487 Times in 2,283 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Create a query from two tables that autofills column

Are one or both fields memo (Aka Long Text) datatype?
If so, any of the values more than 255 characters? If not, change to Text datatype so you can do the join.

If that's not possible, we will instead need to use a location ID Field to join the tables.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
templeowls (06-13-2019)
Old 06-13-2019, 04:07 AM   #7
templeowls
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
templeowls is on a distinguished road
Re: Create a query from two tables that autofills column

You were right! Thank you sir!!!

templeowls is offline   Reply With Quote
Old 06-13-2019, 07:02 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,255
Thanks: 105
Thanked 2,487 Times in 2,283 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Create a query from two tables that autofills column

You're welcome. If this is now sorted, can you please mark it as solved using the thread tools dropdown

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs 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
Create Query for calculated column yasmeen Forms 32 01-10-2011 07:14 PM
Create 9.99 column with SQL query inoxo Modules & VBA 2 01-22-2008 12:26 AM
Create Report from one column in different tables Snowman88 Reports 12 04-16-2007 07:46 AM
create a new row for each table column in query Eyor Queries 11 11-01-2006 08:55 AM
create calc column in query based on criteria from another column mdbBound Queries 2 03-05-2004 08:07 AM




All times are GMT -8. The time now is 02:43 AM.


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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World