Queries :: Calculated Query Column Cannot Group

Jun 9, 2013

I am trying to group the following Query to find duplicates with no success.

Base Query:

SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));

Find Duplicates Query:

SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];

View Replies


ADVERTISEMENT

Queries :: Running Count In A Query By Group - Error In Ranking Column Result

Mar 16, 2013

Query is based on 1 table" tblTimeCnv_AgeGroups

Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4

See attachment

My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])

In the Ranking column the result is: #error in the first 3 rows then zeroes

View 3 Replies View Related

Counting Distinct Values Of A Column Group By Another Column

Dec 4, 2011

I have this table ("people") and an example of possible rows:

id(key) COL 1 COL2 department country name
1 xx yy KPP USA John
2 zz kk KPP USA John
3 ss ff TLL USA John
4 ww qq PPO Italy Marco
5 jj uu PPO Italy Marco

I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).

I have tryed in this way:

SELECT
COUNT(DISTINCT department) AS NumberOfDifferentDepartments
FROM people
GROUP BY name;

But Access says me there is a syntax error.

I'm working with MS Access 2002.

View 2 Replies View Related

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

Queries :: Change In Column Based On Base Query Column

Mar 24, 2014

I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...

Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".

The error is because the second layer of query does not identifies Q1 2014.

How do i make access change the column automatically when the Q1 changes to Q2...

View 1 Replies View Related

Queries :: Add A Column In A Query That Will Give Y Or No To Previous Column

May 21, 2015

I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).

View 3 Replies View Related

Queries :: Group By Function In A Query

Apr 8, 2014

I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.

View 2 Replies View Related

Queries :: Query Group By Hours

Aug 10, 2014

I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.

View 1 Replies View Related

Queries :: Union Query - Group By Then Sum

Aug 5, 2013

I have a Union Query which the result was like this:

Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00

Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date.

View 5 Replies View Related

Queries :: Calculated Field In A Query

Mar 23, 2015

This is not the normal calculated fields in query's. What I want is different, I want it to be like

IFF(ShowID="A",[TotalSales],0)

Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?

View 6 Replies View Related

Queries :: Calculated Date In Query

Jun 4, 2014

I've been struggling with this for a while, and even though I understand the theory, I can't get my code to work correctly.

I have 2 fields:
CallbackFrequency & Last Contacted.

I want to use these fields to populate a third field (callBackOn) so that we can have a list of candidates that need to be called from a certain date.

CallbackFrequency is added from a combobox, so all values are either 1 Month, 3 Months, 6 Months, or 12 Months.

I have tried creating a calculated field directly in the candidates table, but apparently this is not possible.I've therefore tried to create a query using IIF statement that will calculate this value.To get to grips with the code, I'm only dealing with 1 callbackFrequency at the moment. So far I have:

CallbackOn: IIf([Candidates].[CallbackFrequency]="1 Month", [LastContacted],)

I have not factored in the date manipulation yet, as the query does not display the LastContacted date of the record it finds, it only shows a blank cell,

View 8 Replies View Related

Queries :: Calculated Field In A Query

Nov 25, 2013

I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.

View 1 Replies View Related

Queries :: Query Group By And Display Other Columns

Sep 2, 2013

I have a table with 4 columns :

Product Price Quantity Supplier

Product1 5 240 A
Product1 7 19 B
Product1 6 12 C
Product2 96 0 A
Product2 98 23 B
Product2 99 44 C

There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :

Product1 5 240 A
Product2 98 23 B

In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.

View 2 Replies View Related

Queries :: Duplicate Count From Group By Query

Jul 17, 2014

I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:

SELECT Count([summary].Key) AS CountKey
FROM Summary, lookup
WHERE ([Region])) Between [Region 1] And [Region 2]));

View 4 Replies View Related

Queries :: Dataset - Group By Query Without Summing

Jan 15, 2015

I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.

Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.

View 4 Replies View Related

Queries :: Running Count Over Group In Query

Feb 2, 2015

I have an access query named "leaveapp" and I want a running count as below:

EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1

and i need a column in the right with running count like below

EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1

View 3 Replies View Related

Queries :: Sum Option Group Fields In Query

Mar 26, 2014

I had to modify how I was storing data on one of my tables. I changed checkboxes to an option group with the values ranging from 1 to 4.I need to count each of these entries for a report which will run them into another formula.I am trying to use the below - obv I'm doing something wrong.

Code:
Police: Sum(IIf([OType],1,0))

And this is the original SQL for my queries if needed:

qry_BLT_RCCounts1

Code:
SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter

[code]....

View 5 Replies View Related

Queries :: Group Query By Day Of Selected Month

Jul 21, 2015

I am trying to make a query that counts the orders on a day of an specific month selected from a form.

The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.

Any way of making a query that adds the days with no records with a count value of 0?

View 3 Replies View Related

Queries :: Numbering Records By Group In A Query

Jun 2, 2013

I have a database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items

I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.

After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.

Is that possible?

View 2 Replies View Related

Queries :: Group By Part Of Field In Query

Jun 26, 2015

I am trying to group records in a query and count them. I have records containing ABC12345

ABC67890
ABE12345
ABE67890

Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.

View 3 Replies View Related

Queries :: Select Count And Group Query

Sep 19, 2014

I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".

Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.

only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.

View 14 Replies View Related

Queries :: Running Count Over Group In Query

May 11, 2014

I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..

So far this thread has been very useful: [URL] ....

It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).

In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").

The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])

So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.

Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.

View 5 Replies View Related

Queries :: Calculated Date Field In Query

Sep 10, 2014

I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to

AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))

This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA

View 14 Replies View Related

Queries :: Sum Calculated Query Without GroupBy Totals?

Jul 11, 2013

Im trying to run the following calculated query:

Total Value on order: Sum([Purchase Price])

I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function

View 2 Replies View Related

Queries :: Filter Calculated Query Field

Nov 13, 2014

So I have the following query field which calculates another field.

How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].

Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")

View 8 Replies View Related

Queries :: Selecting A New Calculated Field In Same Query

Aug 30, 2013

I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example

SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);

Can I not select a value that has been calculated this query?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved