How to display cell items point by point in report? (1 Viewer)

V

vent

Registered User.
Local time
Today, 08:43
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: 89
P

plog

Banishment Pending
Local time
Today, 07:43
Joined
May 11, 2011
Messages
11,634
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.
 
V

vent

Registered User.
Local time
Today, 08:43
Joined
May 5, 2017
Messages
160
plog said:
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.
Click to expand...

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: 78
  • tablesrelationships.PNG
    tablesrelationships.PNG
    17.7 KB · Views: 75
P

plog

Banishment Pending
Local time
Today, 07:43
Joined
May 11, 2011
Messages
11,634
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.
 
V

vent

Registered User.
Local time
Today, 08:43
Joined
May 5, 2017
Messages
160
plog said:
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.
Click to expand...

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.
 
P

plog

Banishment Pending
Local time
Today, 07:43
Joined
May 11, 2011
Messages
11,634
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.
 
V

vent

Registered User.
Local time
Today, 08:43
Joined
May 5, 2017
Messages
160
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: 75
  • append.PNG
    append.PNG
    11.9 KB · Views: 84
  • 42.PNG
    42.PNG
    9.3 KB · Views: 81
Last edited:
P

plog

Banishment Pending
Local time
Today, 07:43
Joined
May 11, 2011
Messages
11,634
I don't really understand what you mean by "point by point". Can you demonstrate it with data?
 
V

vent

Registered User.
Local time
Today, 08:43
Joined
May 5, 2017
Messages
160
plog said:
I don't really understand what you mean by "point by point". Can you demonstrate it with data?
Click to expand...

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
 
P

plog

Banishment Pending
Local time
Today, 07:43
Joined
May 11, 2011
Messages
11,634
Once you get them into the structure I laid out that's how they will be. Each code on its own row.
 
V

vent

Registered User.
Local time
Today, 08:43
Joined
May 5, 2017
Messages
160
plog said:
Once you get them into the structure I laid out that's how they will be. Each code on its own row.
Click to expand...

Is this correct?
 

Attachments

  • append2.PNG
    append2.PNG
    12.9 KB · Views: 71
P

plog

Banishment Pending
Local time
Today, 07:43
Joined
May 11, 2011
Messages
11,634
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.
 
V

vent

Registered User.
Local time
Today, 08:43
Joined
May 5, 2017
Messages
160
It's more records. I got it now. Thank you very much.
 
You must log in or register to reply here.

Similar threads

M
Solved Report not displaying the same info as query from which it was created.
Replies
8
Views
257
Pat Hartman
P
G
How to add an arrow onto a cell?
Replies
1
Views
282
The_Doc_Man
The_Doc_Man
isladogs
Access Europe Meeting - Wed 1 Nov - Using SQL Server skills to extend Access
Replies
2
Views
551
isladogs
isladogs
N
How to update an existing document with record from open recordset details in Ms Access by criteria
Replies
15
Views
920
Pat Hartman
P
M
Solved Event Click no longer working on a header label on a form
Replies
15
Views
526
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom