Create a query from two tables that autofills column (1 Viewer)

gojets1721

Registered User.
Local time
Today, 11:59
Joined
Jun 11, 2019
Messages
429
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
 

theDBguy

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

gojets1721

Registered User.
Local time
Today, 11:59
Joined
Jun 11, 2019
Messages
429
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
 

isladogs

MVP / VIP
Local time
Today, 18:59
Joined
Jan 14, 2017
Messages
18,186
Link the location fields from both tables in your query
 

gojets1721

Registered User.
Local time
Today, 11:59
Joined
Jun 11, 2019
Messages
429
When I do that, I get the error "Cannot join on Memo, OLE, or Hyperlink ([Events].[Store Locations]=[Managers].[Store Locations])"
 

isladogs

MVP / VIP
Local time
Today, 18:59
Joined
Jan 14, 2017
Messages
18,186
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.
 

isladogs

MVP / VIP
Local time
Today, 18:59
Joined
Jan 14, 2017
Messages
18,186
You're welcome. If this is now sorted, can you please mark it as solved using the thread tools dropdown
 

Users who are viewing this thread

Top Bottom