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

Bean Machine

Member
Local time
Today, 16:48
Joined
Feb 6, 2020
Messages
98
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.
 

Attachments

  • Prompt example.png
    Prompt example.png
    6.8 KB · Views: 476
  • Grouping code desc example.png
    Grouping code desc example.png
    11.4 KB · Views: 378

theDBguy

I’m here to help
Staff member
Local time
Today, 13:48
Joined
Oct 29, 2018
Messages
21,358
Hi. To understand what you're trying to do, can you please post a mockup data result from your query using Excel? Thanks.
 

plog

Banishment Pending
Local time
Today, 15:48
Joined
May 11, 2011
Messages
11,611
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

Member
Local time
Today, 16:48
Joined
Feb 6, 2020
Messages
98
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.
 

Attachments

  • GCU query info.png
    GCU query info.png
    8.1 KB · Views: 455

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:48
Joined
May 21, 2018
Messages
8,463
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.

Code:
tblTags
  tagID
  tagDescription

So you might have something like
Code:
1 Computers
2 Aerospace
3 Environmental
...

Assume you have a table of projects and you want to assign Tags (key words) to them
Code:
tblProjects_Tags
  ProjectID_FK
  TagID_FK

Example
Code:
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
Code:
11 2
15 2
22 2
 

plog

Banishment Pending
Local time
Today, 15:48
Joined
May 11, 2011
Messages
11,611
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

Member
Local time
Today, 16:48
Joined
Feb 6, 2020
Messages
98
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

Banishment Pending
Local time
Today, 15:48
Joined
May 11, 2011
Messages
11,611
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

You've got your good things, and you've got mine.
Local time
Today, 16:48
Joined
May 21, 2018
Messages
8,463
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

Super Moderator
Staff member
Local time
Today, 16:48
Joined
Feb 19, 2002
Messages
42,976
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.
 

Users who are viewing this thread

Top Bottom