How to display cell items point by point in report?

vent

Registered User.
Local time
Today, 19:57
Joined
May 5, 2017
Messages
160
Hi everyone

Below is a screenshot of a column called "Codes" on a report and as you can see, some of them are separated by commas. Is there a way for me to display these point by point (code by code) on a report?

E.g.

  • 0008

  • '1077

  • '1165

Any thoughts or suggestions are welcomed and appreciated!
 

Attachments

  • codesreport.PNG
    codesreport.PNG
    1.3 KB · Views: 112
I advise storing them properly. Each discrete piece of data needs to be stored in its own field. You have a 1-many relationship, you need a new table to hold the data that goes into that field.
 
I advise storing them properly. Each discrete piece of data needs to be stored in its own field. You have a 1-many relationship, you need a new table to hold the data that goes into that field.

Okay so I have a table with these codes stored in them. As seen below, and to store them properly in the query are you saying I need to use this table instead of the main one aka (AgencyINFO)?
 

Attachments

  • codestable.PNG
    codestable.PNG
    8.3 KB · Views: 96
  • tablesrelationships.PNG
    tablesrelationships.PNG
    17.7 KB · Views: 92
The way you are using (not using) your ID fields makes that incorrect. When you have primary keys on tables (ID fields) you use those to JOIN them, not other fields.

If you want to use tblProgramCodes (which I think is a definition table of what your codes are). You are going to need a junction table to sit between tblProgramCodes and AgencyInfo. Let's call this new table AgencyProgramCodes. It's structure will be this:

apc_ID, autonumber, primary key
apc_CodeID, number, links to tblProgramCodes.ID
apc_Agency, number, links to AgencyInfo.ID

That's it. Then to assign codes to an agency you fill in as many records in AgencyProgramCodes as you have codes. That means if you have 3 values jammed into a field now, they become 3 records in AgencyProgramCodes.
 
The way you are using (not using) your ID fields makes that incorrect. When you have primary keys on tables (ID fields) you use those to JOIN them, not other fields.

If you want to use tblProgramCodes (which I think is a definition table of what your codes are). You are going to need a junction table to sit between tblProgramCodes and AgencyInfo. Let's call this new table AgencyProgramCodes. It's structure will be this:

apc_ID, autonumber, primary key
apc_CodeID, number, links to tblProgramCodes.ID
apc_Agency, number, links to AgencyInfo.ID

That's it. Then to assign codes to an agency you fill in as many records in AgencyProgramCodes as you have codes. That means if you have 3 values jammed into a field now, they become 3 records in AgencyProgramCodes.

Hi, first of all, I'm sorry for the late reply. Things are hectic. But thanks for the reply regardless. Now I made a table called what you suggested, made those three fields but my question is how do "link to" table? (e.g. links to tblProgramCodes.ID)? Because right now, it's just an empty table with 1 autonumber and 2 number data types.
 
When I use "links to" I use it as a mental construct. In your mind those 3 tables are linked to sort out what code goes to what program.

Technically, in a query those would be the fields on which you JOIN. For example:

...FROM tblProgramCodes INNER JOIN AgencyProgramCodes ON apc_CodeID = ID ...


As for the tables being empty--that has nothing to do with linking them. That's going be a process of moving your data from where it is now into that new table. APPEND queries or even exporting it out to Excel, manipulating it there and re-importing it into AgencyProgramCodes.
 
Okay. I youtubed append queries and I think I followed the steps correctly. Here are a couple screenshots of what I've done and here is the SQL:
Code:
INSERT INTO AgencyProgramCodes ( apc_CodeID, apc_Agency )
SELECT tblProgramCodes.ID, [AgencyINFO-main].ID
FROM AgencyProgramCodes, tblProgramCodes INNER JOIN [AgencyINFO-main] ON tblProgramCodes.ProgramCode = [AgencyINFO-main].ProgramCodes;

I guess going back to my point and sorry for being a noob but how do I use this new agencyprogramcodes table to make reports point by point?

Also I just looked at the main table and noticed this random 42 in place of a previous value (1343). Most of the other values look correct to what was there originally but I'm just curious of what this random 42 means?
 

Attachments

  • agencyprogramcodes.PNG
    agencyprogramcodes.PNG
    25.2 KB · Views: 90
  • append.PNG
    append.PNG
    11.9 KB · Views: 100
  • 42.PNG
    42.PNG
    9.3 KB · Views: 96
Last edited:
I don't really understand what you mean by "point by point". Can you demonstrate it with data?
 
I don't really understand what you mean by "point by point". Can you demonstrate it with data?

On the reports the program codes are separated by a comma. Instead of it being displayed this way (1232, 2212, 3019, etc.). How am I able to display them as as bullet points?

  • 1232

  • 2212

  • 3019
 
Once you get them into the structure I laid out that's how they will be. Each code on its own row.
 
Once you get them into the structure I laid out that's how they will be. Each code on its own row.

Is this correct?
 

Attachments

  • append2.PNG
    append2.PNG
    12.9 KB · Views: 87
I honestly don't know. I don't work with multi-value fields, so I don't know how those with multiple values are going to work in a JOIN.

How many records does that query produce? Is it more than the number of records in AgencyInfo-Main? If not, then its probably wrong.
 
It's more records. I got it now. Thank you very much.
 

Users who are viewing this thread

Back
Top Bottom