Solved Is there any way to make a field specifically for entering data (similar to a table) in a query?

[Bean Machine]

I want to add a field called "Grouping Description" to a query that will detail the meaning of something called a grouping code (basically tags). The reason I want to do this in the query is because it contains only the distinct grouping codes so adding the description there would be much easier than trying to add the same descriptions over an over in the table data it pulls from (as the grouping codes are not unique and are attached to many different items). I've tried using this "Grouping Description: ([Grouping Description])" as the field name but the prompt that comes up adds the value I enter to all of the grouping codes. Basically, is there a way to do this that I'm missing? I'll include some images for reference.

 

[theDBguy]

Hi. To understand what you're trying to do, can you please post a mockup data result from your query using Excel? Thanks.

 

[plog]

My guess is the query that query is built on (qry_CD) is the one with the issue. Can you post a screenshot of it?

Also, you are doing yourself no favor by using spaces and parenthesis in field names (e.g. [Grouping Code], [SumOfAmount (c)]). Those could be causing an issue too.

 

[Bean Machine]

Apologies I attached the wrong reference image, that was for a different query entirely. I just updated the image, I can provide an image of the query data as well. See image for details on the query design.

 

[MajP]

I am not sure I understand, but will guess since you mention Tags. Normally if are associating Tags/Key words you have a many to many structure. Many tags can be associated to a record, and a record can have many tags.

tblTags
  tagID
  tagDescription

So you might have something like

1 Computers
2 Aerospace
3 Environmental

...

Assume you have a table of projects and you want to assign Tags (key words) to them

tblProjects_Tags
  ProjectID_FK
  TagID_FK

Example

1 2
1 3
1 7
2 1
2 3

Project 1 is associated with the tags Computers, Aerospace, and whatever 7 is.

So if I had a query that returns a group of records and I want to bulk tag them, then I would run an insert query that adds a record for each item in the query and the selected tag. If the query returns records 11, 15, 22, and I want to tag them all as Aerospace it does an insert query producing

11 2
15 2
22 2

 

[plog]

You can't use fields in a query that don't exist in the underlying datasources that make up the query. [Grouping Description] is not in tblReq. When you run the query it has no idea what [Grouping Description] is so it asks you for the value.

 

[Bean Machine]

You can't use fields in a query that don't exist in the underlying datasources that make up the query. [Grouping Description] is not in tblReq. When you run the query it has no idea what [Grouping Description] is so it asks you for the value.

Okay I didn't think so. What would be the best way for me to go about adding a description field in a table for the grouping codes? Ideally it would be for unique grouping codes only.

 

[plog]

Adding fields to a table is simple--open the table in design view, add a new field and save the table. Did I missing something--that seems like an odd question.

 

[MajP]

What would be the best way for me to go about adding a description field in a table for the grouping codes? Ideally it would be for unique grouping codes only

If it is a single code per record than add a field. If it is many codes per record then add a child table with a foreign key. If it is a many to many I already provided the answer.

 

[Pat Hartman]

Forms/Reports don't store data. Tables store data. Create a table that defines the groupID and its description. Then the report can be based on a query that joins the two tables.