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
> Show Amount Only Once In Select Query That Joins 2 Tables, Access 2013    
 
   
bakersburg9
post Sep 9 2019, 05:53 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I have query that is sourced by a 130,000 record table with an inner join on the job Number of a much smaller table that only has one job number (db Attached)
I’m using a simple query creating a data set that brings in most of the fields from both tables, so obviously there is a lot of redundancy
I think I’ve asked a somewhat similar question before, and the answer was to create a report – but I tried (unsuccessfully) using MAX to just have one of these “duplicates” show up – is that possible, maybe creating a separate update query to update just one record PER JOB# ?

The main issue is the “Job-To-Date Costs” on the “One” side – to have this amount just show up once (PER JOB) in my select query results ?


EDIT: another POSSIBLE solution is to run an update query, but so it only updates one value ? (I wouldn't know where to start on the mechanics of that...

Thanks in advance,
Steve
This post has been edited by bakersburg9: Sep 9 2019, 06:09 PM
Attached File(s)
Attached File  UA_ChallengeShowTotal.zip ( 1.65MB )Number of downloads: 6
Attached File  UA_Challenge_Show_Once.png ( 49.49K )Number of downloads: 8
 
Go to the top of the page
 
Doug Steele
post Sep 9 2019, 06:17 PM
Post#2


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


The problem is that there are multiple rows in CostZeroBilling_Q1toQ3_2017_19 with the same value for Job. Consequently, you get rows repeated.

One way to solve the problem would be to limit the query to unique records.

You can do that by adding the keyword DISTINCT to the SQL of the query:

CODE
SELECT DISTINCT CostZeroBilling_Q1toQ3_2017_19.Job, CostZeroBilling_Q1toQ3_2017_19.[Job Name], CostZeroBilling_Q1toQ3_2017_19.AccountingDate, LostJobsWithJobCost_2016_2019.[Date Taken], LostJobsWithJobCost_2016_2019.[Job-to-Date Costs], LostJobsWithJobCost_2016_2019.[Lost Job Description], LostJobsWithJobCost_2016_2019.[Lost Job Reason]
FROM CostZeroBilling_Q1toQ3_2017_19 INNER JOIN LostJobsWithJobCost_2016_2019 ON CostZeroBilling_Q1toQ3_2017_19.Job = LostJobsWithJobCost_2016_2019.[Job Number];


or by looking at the properties of the query and setting the Unique Values property to Yes:

Attached File  UA_ChallengeShowTotalOnce_Query.jpg ( 123.22K )Number of downloads: 0


Of course, this may not be what you want: you're still going to get some rows duplicated because the values in other fields are different for the same job:

Attached File  UA_ChallengeShowTotalOnce_Results.jpg ( 118.26K )Number of downloads: 1


If that's not what you want, perhaps explain what it is that you're trying to do, and we can offer a solution.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
bakersburg9
post Sep 10 2019, 09:26 AM
Post#3



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Doug, I don't think I explained what I wanted very well, but Thanks for chiming in!

Steve
Go to the top of the page
 
RJD
post Sep 10 2019, 11:17 AM
Post#4


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi Steve: I took a look at your db and requirements - and let's see if I interpreted this correctly. You were listing jobs with transactions in a query, and the total cost amount was showing for each record result rather than just once. You wanted it to show just once for a job. Is that correct?

There are ways to deal with this is a report, but let's stick to the query method for now. I used a query set to isolate each job with its latest accounting date and the associated detail record ID. Then I added this to an existing query and used logic to display the job cost to date if the IDs matched. This makes the cost available only to one record per job, with 0 for the others.

See q_CostNoBilling_Lost_Jobs_OneCostPerJob. See if this is what you are trying to do - or if we need to explore the requirement further.

HTH
Joe
Attached File(s)
Attached File  UA_ChallengeShowTotalOnce_Rev1.zip ( 1.49MB )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
arnelgp
post Sep 10 2019, 11:41 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


I think I have same result as RJD.
made a query q_minOfID and link it to q_CostNoBilling_Lost_Jobs_MAX.
see q_CostNoBilling_Lost_Jobs_MAX query.
Attached File(s)
Attached File  UA_ChallengeShowTotal.zip ( 1.58MB )Number of downloads: 10
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 12:47 PM
Post#6



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


RJD and Arne,
What I'm doing is exporting the results of a query to excel - there is one field from a data source with just one "JobCostTotalToDate" field - these totals are based on the job number - one total spend per job - but the dataset has individual amounts - I only want to have this amount once, so I can compare the two fields - if necessary, I could have my select query make a table, and then go in and delete all total job cost to date amounts for each job except one job - so I can compare the actual job costs grouped by job number to whatever the amount is in this "total job cost to date" field.

Thanks!

Steve
Go to the top of the page
 
RJD
post Sep 11 2019, 05:26 PM
Post#7


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi Steve: Hmmm ... I thought that was what I did. Did you take a look at [q_CostNoBilling_Lost_JobsOneCostPerJob]? You can always export these results if you want.
How is what you want different from this result? If the requirement IS different from what the query produces, perhaps you could show us a picture of what it should actually look like.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 07:12 PM