Create Table Query: query pulling mix of unique ID and name

[dswift]

I am trying to make a "create table query". The purpose of this new query is to look at a larger table, and only pull client name; Analyst; and Reviewer. When I look at the query datasheet view, everything looks fine, but when I "run" the query and the new table is created, I run into issues. Under the "Analyst" column, I only see the unique ID instead of employee name. Under the "review" column, some of the employee names show, but others show as the unique ID (for example, employee 9 always shows up as '9', and the rest of the reviewers names show). In the underlying table, the name (not unique ID) is shown.

CLIENT NAME	ANALYST	REVIEWER
Client A	3	Michael
Client B	4	Lisa
Client C	2	Melissa
Client D	7	9

<tbody>
</tbody>

 

[Joe4]

If you have a Query that returns what you need, why are you trying to create a Table from it?
Usually, you can use the Query for whatever needs you may have. Things like Forms, Reports, and Exports can be created from Queries just as easily as they can with Tables.

 

[dswift]

I am new to access to maybe this is not the best way. I am looking to replace a manual spreadsheet I make in excel each month. Essentially I have clients for which we provide services for at certain times throughout the year. (some clients may be once a year, some each month). Each month I would like to have a list of clients we are servicing that month, show employee responsible, and I'd like columns for the employee to type in dates that specific tasks are completed (see the example below). I think I need a make-table query to be able to do this each month.

Right now I have:
1) Client Table: Includes client names and the assigned employee (lookup from Employee Table)
2) Service table: a list of services we provide
3) Month table: just a list of months
4) Client_service_Month table: The primary key is a combination of client name_Service type_month. This is the table I am using as the source of my query.


For each month, generate a table with:

CLIENT (Generated from table 4 subset)	PREPARER (Generated from table 4 subset)	REVIEWER (generated from table 4 subset)	DATE INFO RECEIVED (Type in Date)	DATE DUE (would like this to be calculated from date received + turn around days per contract)	TASK A COMPLETED (Type in DATE)	TASK B COMPLETED (Type in DATE)
CLIENT A	JOE SMITH	BOB BROWN
CLIENT B	MELISSA WHITE	BOB BROWN

<tbody>
</tbody>

 

[Joe4]

I don't think you want to use a Make Table Query. The reason being, is that then every time you run this, you will be creating a new Table. A well designed database does not add new Tables or Fields to Tables often. It is usually should be fairly "stable", when it comes to those things.

Since you will be adding the same fields each time you run this, you should really just have one table that this goes to. So, you would use an Append Query to write the information to an existing table.
You might need to add a new field to your table that you hadn't counted on originally in order to easily differentiate and segregate the records from your different runs. Maybe a date field, or something along those lines.

 

[dswift]

Okay, I see your point and have set it up this way. However, my original issue still remains in that when look at the append query in datasheet view all my information looks fine. As soon as I "run" the query and append to the new table, I am getting the numbers instead of names in some cases as described in my original post - any ideas on whats causing this?

 

[Joe4]

These fields that you are having issues with, how are they set up in the original table?
Are they reference or look-up field?

 

[dswift]

Yes they are a lookup field populated using a combo box

 

[Joe4]

That is the issue then. It is returning the bound column and not the display column. That is expected behavior. So it is just an understanding of what is happening.

Many people warn about using lookup fields in table like that, as it can be very confusing. There are arguments on both sides of the fence.
Here is one that talks about the issues, and a rebuttal to it:
http://access.mvps.org/access/lookupfields.htm
https://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/

So, you can use them, but you need to then understand the expected behaviors of doing so.

Note: If you use an Append Query to an existing table, you should be able to set up those fields as lookup fields the same way as your original table, and then hopefully, it will look a little less "wonky" to you.

 

[dswift]

Thanks, those articles were helpful. I think I got it working too!

 

[Joe4]

Great, glad to hear it.

Out of curiosity, what did you end up doing?