Correct Quarrie Syntax Question (1 Viewer)

marseneault · Jan 17, 2019

So this seams to be an easy request, but I can't figure out how to word it.

My boss is doing Performance Evaluations for everyone and wanted to Cross Reference Each Contractor with the Job That they worked on. The only way I know for sure is by checking to see if they have a time clock record.

I am looking for the right syntax string to basically Give this result:

If Contractor Has TimeClock Record, Show ProjectName, ProjectSalesTotal and ProjectSalesProfit.

The Syntax that I did use Listed all the TimeClock Records, so if the person worked 5 times on a job, it would list the Project Name, SalesTotal and Profit 5 times instead of one.

Ranman256 · Jan 17, 2019

Drag the fields onto the query Grid,
Then add criteria under the field needed.

plog · Jan 17, 2019

Assuming you have one simple table where all this data is, this SQL will do it:

Code:

SELECT Contractor, ProjectName, ProjectSalesTotal, ProjectSalesProfit
FROM YourTableNameHere
GROUP BY Contractor, ProjectName, ProjectSalesTotal, ProjectSalesProfit

If not, give us specifics.

marseneault · Jan 17, 2019

Thanks for your help,

I understand that part, but what is the correct SQL Statement to tell it to give me results A if B is "present" I tried several combinations and could not get it to work correctly.

marseneault · Jan 17, 2019

Thanks for the right Syntax, The Information is in 2 tables, one Titled: Access_JobsiteTable has the list of Projects and their Financials,

The Other is Titled: Access_TimeClock
It has the Contractor's and Their Timesheets for each Job

They both use the Field: JobsiteID as the reference Key

Thanks again for your assistance.

I hope that this helps

plog · Jan 17, 2019

what is the correct SQL Statement to tell it to give me results A if B is "present...

SQL achieves that not through logic coding, but through JOINs.

tblSalesPeople
SalesPersonID, FirstName, BirthDate
1, Steve, 1/1/1992
2, Jim, 2/2/1996
3, Dave, 3/3/2001

tblSales
SalesPersonID, SalesAmount, SalesDate
1, 50, 1/1/2018
1, 17, 2/2/2018
3, 99, 3/1/2018
1, 4/1/2018

There's no logic coding to get Sales Total by SalesPerson such that anyone without sales doesn't show up (Jim). You achieve this through a JOIN and the data makes it happen:

Code:

SELECT FirstName, SUM(SalesAmount) AS SalesTotal
FROM tblSales
INNER JOIN tblSalesPeople ON tblSales.SalesPersonID = tblSalesPeople.SalesPersonID
GROUP BY FirstName;

There's no if statements, the act of making them JOIN makes it so that Jim doesn't show up, because he doesn't have data.

If we wanted to exclude salesmen who were born after the turn of the century we could add a WHERE clause to accomplish that and Dave falls out.

So, my suggestion is to work through a few basic query tutorials (https://www.w3schools.com/sql/) and then give it a shot on your data. Then post back here what code you have tried.

marseneault · Feb 4, 2019

I am not use to using code so I apologize if this question seams simple but do I use the expression Builder in my query to put this string in?

marseneault · Feb 4, 2019

Here is the syntax that I used in the Espression Builder Dialog box. It keeps giving me errors so I am almost sure that I am doing something wrong

SELECT [Access_TimeClock].[ContractorName], [Access_JobsiteTable].[Name]
FROM [Access_TimeClock]
INNER JOIN ContractorName On [Access_TimeClock].[JobsiteId]=[Access_JobsiteTable].[JobsiteID]
GROUP BY ContractorName;

Access_TimeClock is the table that the timeclock data is
Access_JobsiteTable is where the Projects and Profit are stored.
ContractorName is the name of the Contractor in the TimeClock Table
JobsiteId is the Common Field that Join the two tables

plog · Feb 4, 2019

Every field in the SELECT clause must either have an aggregate function on it (SUM(), COUNT(), MAX() etc.) or be in the GROUP BY clause. [Access_JobsiteTable].[Name] does not adhere to that and is causing the error.

Either add [Access_JobsiteTable].[Name] to the GROUP BY or remove the GROUP BY completely.

Lastly, "Name" is a poor choice for a field name--it's a reserved word and will cause issues when coding or querying. Plus its super generic--everything has a name. What's it the name of?

marseneault · Feb 5, 2019

Thanks for the assistance. It is very helpful.
I agree about the word Name, it is very common and not descriptive at all. I will see about changing it.

marseneault · Feb 5, 2019

So I changed it up to get rid of the name and now have 2 tables, One has the Time sheet, the other The Jobsite Profit and Financial info. I am trying to use the right expression, but keep getting the same errors:

SELECT [Access_TimeClock].[ContractorName],SUM([Access_JobsiteProfit].[Profit] AS ProfitTotal
FROM [Access_TimeClock]
INNER JOIN ContractorName On [Access_TimeClock].[JobsiteId]=[Access_JobsiteProfit].[JobsiteID];
GROUP BY [ContractorName};

Did I write this up correctly? I have a feeling that I am only off by a little, but I keep getting back errors.

plog · Feb 5, 2019

No

1. You are missing a closing parenthesis around your SUM()

2. The first object after your INNER JOIN should be a table/query name, not a field name.

3. You've got the wrong kind of ending bracket in your GROUP BY

Why are you not using the query designer to build this?

marseneault · Feb 5, 2019

Hello Plog,

I am using the Expression Builder in Access and have reviewed several tutorials on the w3schools site. I am unfortunately, not a very good programmer.

Here is the updated code:

(SELECT [Access_TimeClock].[ContractorName],SUM( [Access_JobsiteProfit]![Profit]) AS PROFITTOTAL
FROM [Access_JobsiteProfit]
INNER JOIN [Access_TimeClock] ON [Access_TimeClock].[JobsiteId]=[Access_JobsiteProfit].[JobId]
GROUP BY ([ContractorName])

The error that I now get is:
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

Thanks again for your patience in helping me with this.

plog · Feb 5, 2019

The SQL you posted is a valid query and should work. The problem is the context in which you are using it.

I have no idea where this thing exists. Seems you are trying to do more than what you have shown to me and the error exists outside what you have presented.

If you paste the above SQL into a new query it will produce results.

marseneault · Feb 5, 2019

Thanks for your reply,

I have this expression in the Criteria box of a select query that I have named "EmployeeReviewJobsiteSelector"

There are 2 tables in the querry: Access_TimeClock & Access_JobsiteProfit.

There is a one to one relationship created between the 2 using JobID

The Query is supposed to give me the list of Jobs that each contractor has clocked into. There are many instances where a contractor clocked into the same job more than once, hence the reason that you advised me use the Join Query.

IN the Select Query, I have the following fields:

ContractorName
Job
JobId
JobrunningTotal (How much the Job Sold for)
Profit ( How much it made)

plog · Feb 5, 2019

I have this expression in the Criteria box of a select query that I have named "EmployeeReviewJobsiteSelector"

That doesn't make sense. Can you post a screenshot or even the database itself?

marseneault · Feb 7, 2019

Here is a screen shot. The database is larger than this forum will allow for an attachment. Thanks again for your help.

plog · Feb 7, 2019

Your building a query within the expression builder of design view of another query? Don't do that.

Open the design view to a new query and then build the query you are building in the expression builder there.

marseneault · Feb 7, 2019

:banghead:
I Created the new query as you recommended, in the field selector, do I need to have the ContractorName, the JobID?
When I paste the expression in a new query, I get the same results

plog · Feb 7, 2019

Don't use the SQL that doesn't work. Just use the query builder to recreate it.

Bring in both data sources
JOIN them
Bring down the fields you want to use
change the query to a Totals query
change the Total row below each field to the approprate value

Correct Quarrie Syntax Question (1 Viewer)

Registered User.

Well-known member

Banishment Pending

Registered User.

Registered User.

Banishment Pending

Registered User.

Registered User.

Banishment Pending

Registered User.

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Attachments

Banishment Pending

Registered User.

Banishment Pending

Similar threads

Users who are viewing this thread