UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> One To Many Issue And Building Query, Access 2010    
 
   
pbgradyD
post Apr 20 2017, 07:56 AM
Post#1



Posts: 28
Joined: 11-March 16



Hello

I have 2 tables, 1 has all unique records with a unique record that I can link to a table with many records per unique value

I want to create a query or VB, that would give me the the 1 unique record and however many other records are attached to it

For example, this would be my unique record: R-7777
R-7777 would be connected to a table that has many and return this:
ID Value
R-7777 W
R-7777 X
R-7777 Y
R-7777 Z

I would want a way to have the query return ID: 1 Value: 4
I researched and I see DCount but this appears to just be adding up all of my Unique Records together,
Any thoughts on how to single out the unique records
thanks
Go to the top of the page
 
cheekybuddha
post Apr 20 2017, 08:12 AM
Post#2


UtterAccess VIP
Posts: 8,844
Joined: 6-December 03
From: Telegraph Hill


Hi,

You can use an aggregate query.

Please try and give real table/field names when you post - it makes life easier for everyone. Pseudo names tend to confuse things more.
CODE
SELECT ID, Count([Value]) As Quantity
FROM tblMany
GROUP BY ID
ORDER BY ID;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 08:12 AM
Post#3


UA Admin
Posts: 29,088
Joined: 20-June 02
From: Newcastle, WA


You need the Count() function here.

SQL
SELECT table1.t1ID, Count(table2.T2ID)
FROM table1 INNER JOIN table2 ON table1.t1ID = table2.t1ID
GROUP BY table1.t1ID


I used generic names since we can't see the real table and field names in your database.

Note that t1ID refers to the Primary Key in table1 and its related Foreign Key in table2. t2ID refers to the Primary Key in table2.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 08:13 AM
Post#4


UA Admin
Posts: 29,088
Joined: 20-June 02
From: Newcastle, WA


Or the simpler version Dave suggested. grin.gif

--------------------
Go to the top of the page
 
pbgradyD
post Apr 20 2017, 09:14 AM
Post#5



Posts: 28
Joined: 11-March 16



OK
Im not as clever as you all. My unique ID is actually 2 table fields so first I create a calculated field named ASSIGNED_PLAN

I am not sure how to add the SQL or code to get this to work. I am somewhat familiar with code so I tried adding this to a txt field on a form that was created by the query that I am using

Then I tried the SQL technique by adding the SQL code in the SQL view of the query tool

This is my SQL code now just to get the records that I need:

SELECT DISTINCTROW [tbl_PLAN_REVIEWS].[PLAN_CTRL_CD] & "-" & [tbl_PLAN_REVIEWS].[PLAN_CTRL_NUM] AS ASSIGNED_PLAN, tbl_PLAN_SITES.PLAN_SITE_NAME
FROM tbl_PLAN_REVIEWS INNER JOIN tbl_PLAN_SITES ON (tbl_PLAN_REVIEWS.PLAN_CTRL_NUM = tbl_PLAN_SITES.PLAN_CTRL_NUM) AND (tbl_PLAN_REVIEWS.PLAN_CTRL_CD = tbl_PLAN_SITES.PLAN_CTRL_CD)
ORDER BY [tbl_PLAN_REVIEWS].[PLAN_CTRL_CD] & "-" & [tbl_PLAN_REVIEWS].[PLAN_CTRL_NUM];


Im not sure if this makes it more confusing, but im combining to fields (PLAN_CTRL_CD) and (PLAN_CTRL_NUM) to create my unique ID as S-7777

If i were to use the SQL that u posted I would add it after this in the SQL viewer?
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 09:19 AM
Post#6


UA Admin
Posts: 29,088
Joined: 20-June 02
From: Newcastle, WA


SQL
SELECT DISTINCTROW [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num] AS ASSIGNED_PLAN,
tbl_plan_sites.plan_site_name
FROM tbl_plan_reviews INNER JOIN tbl_plan_sites ON ( tbl_plan_reviews.plan_ctrl_num = tbl_plan_sites.plan_ctrl_num ) AND ( tbl_plan_reviews.plan_ctrl_cd = tbl_plan_sites.plan_ctrl_cd )
ORDER BY [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num];


Change to:

SQL
SELECT [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num] AS ASSIGNED_PLAN,
Count(tbl_plan_sites.plan_site_name) AS SitesAffected
FROM tbl_plan_reviews INNER JOIN tbl_plan_sites ON ( tbl_plan_reviews.plan_ctrl_num = tbl_plan_sites.plan_ctrl_num ) AND ( tbl_plan_reviews.plan_ctrl_cd = tbl_plan_sites.plan_ctrl_cd )
GROUP BY [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num]
ORDER BY [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num];

This post has been edited by GroverParkGeorge: Apr 20 2017, 09:22 AM

--------------------
Go to the top of the page
 
pbgradyD
post Apr 20 2017, 09:33 AM
Post#7



Posts: 28
Joined: 11-March 16



That worked great. Thank you very much, that helped a lot

Go to the top of the page
 
cheekybuddha
post Apr 20 2017, 09:44 AM
Post#8


UtterAccess VIP
Posts: 8,844
Joined: 6-December 03
From: Telegraph Hill


Simpler version - you don't need the join since the same data is in the detail/many table already:
CODE
SELECT
  PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM AS ASSIGNED_PLAN,
  PLAN_SITE_NAME
FROM tbl_PLAN_SITES
GROUP BY PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM
ORDER BY PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ScottGem
post Apr 20 2017, 10:27 AM
Post#9


UtterAccess VIP / UA Clown
Posts: 31,963
Joined: 21-January 04
From: LI, NY


And yet another example of where using generic object names hinders us providing a solution.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 11:01 AM
Post#10


UA Admin
Posts: 29,088
Joined: 20-June 02
From: Newcastle, WA


I think you want a Count() on PLAN_SITE_NAME in this case as well.

--------------------
Go to the top of the page
 
cheekybuddha
post Apr 20 2017, 11:38 AM
Post#11


UtterAccess VIP
Posts: 8,844
Joined: 6-December 03
From: Telegraph Hill


George, I think you are very definitely right! wink.gif Thanks for having my back.

Corrected:
CODE
SELECT
  PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM AS ASSIGNED_PLAN,
  COUNT(PLAN_SITE_NAME) AS SitesAffected
FROM tbl_PLAN_SITES
GROUP BY PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM
ORDER BY PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM;


thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
pbgradyD
post Apr 20 2017, 12:56 PM
Post#12



Posts: 28
Joined: 11-March 16



Ok
I had it working. Did you change the code when you simplified it?

SELECT [tbl_PLAN_REVIEWS].[PLAN_CTRL_CD] & "-" & [tbl_PLAN_REVIEWS].[PLAN_CTRL_NUM] AS ASSIGNED_PLAN, Count(tbl_PLAN_SITES.PLAN_SITE_NAME) AS SitesAffected, tbl_PLAN_SITES.PLAN_SITE_NAME
FROM tbl_PLAN_REVIEWS INNER JOIN tbl_PLAN_SITES ON (tbl_PLAN_REVIEWS.PLAN_CTRL_NUM = tbl_PLAN_SITES.PLAN_CTRL_NUM) AND (tbl_PLAN_REVIEWS.PLAN_CTRL_CD = tbl_PLAN_SITES.PLAN_CTRL_CD)
GROUP BY [tbl_PLAN_REVIEWS].[PLAN_CTRL_CD] & "-" & [tbl_PLAN_REVIEWS].[PLAN_CTRL_NUM], tbl_PLAN_SITES.PLAN_SITE_NAME
ORDER BY [tbl_PLAN_REVIEWS].[PLAN_CTRL_CD] & "-" & [tbl_PLAN_REVIEWS].[PLAN_CTRL_NUM];


I got the correct answer with the original code that you posted, or I think you changed it if not then its my human error on this. I dont think it likes it when you make the 2 tables = based off of the PLAN_CTRL_CD and PLAN_CTRL_NUMB as their are more records in one table and I have criteria added to it

But I didnt save the first code you posted, did you edit it, or am I wrong?
Go to the top of the page
 
pbgradyD
post Apr 20 2017, 01:20 PM
Post#13



Posts: 28
Joined: 11-March 16



It seems to work correctly with this simpler code that you all posted

SELECT
PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM AS ASSIGNED_PLAN,
COUNT(PLAN_SITE_NAME) AS SitesAffected
FROM tbl_PLAN_SITES
GROUP BY PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM
ORDER BY PLAN_CTRL_CD & "-" & PLAN_CTRL_NUM;


But, I need fields from the other table, I just didnt know how to count the SitesAffected
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 01:25 PM
Post#14


UA Admin
Posts: 29,088
Joined: 20-June 02
From: Newcastle, WA


You'' need need to include THIS query as a subquery, then, in a query that brings in the other fields you need.

I'm afraid I have o run out to a meeting, but someone will be here to coach you on that.

We'll need to know, though, what those other fields are and which tale they are in....

--------------------
Go to the top of the page
 
pbgradyD
post Apr 20 2017, 01:27 PM
Post#15



Posts: 28
Joined: 11-March 16



It worked perfect before we changed it, so ill try to go from there, thanks
Go to the top of the page
 
pbgradyD
post Apr 20 2017, 01:58 PM
Post#16



Posts: 28
Joined: 11-March 16



I think it was doubling the records after making the tables equal, im not sure, but I set it back to the original SQL
thank you very much for the help
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd April 2017 - 02:46 PM