Queries :: Remove Grouping On The Field If Do Not Select A Value For It
Sep 4, 2014
I'm trying to allow my users to do some analysis of existing data. they would like to be able to generate a report which will summarize sales orders against the month they were placed. They would also like to summarize this by between 1 and 4 other fields.
I have created a "filter" form to allow them to choose the values for each of the 4 fields and my plan is that they will leave them blank if they do not want to narrow them down. My problem is that I would like to remove the grouping on the field if they do not select a value for it.
Example data:
Field1, Field2, Field3, Field4, Cost, Month, Year
1,2,3,4,£20,4,2014
1,3,4,5,£10,4,2014
1,2,4,5,£5,4,2014
If they select nothing for any of the fields I would just want to see that my total for April 2014 is £35
If they select 1 in Field1 then I want to see one line with a total of £35 (i.e. it ignores the distinction of the other selectable fields)
If they select 1 in Field1 and 2 in Field2 the total would be £25
If they select 2 in Field2 and 4 in Field3 the total would be £5
etc. etc.
I am struggling because I need the distinct records to allow me to group on them when I need to but if I don't need to group on them I need to ignore the grouping.
View Replies
ADVERTISEMENT
Jul 2, 2014
I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:
0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet
This is the query I'm coming up with so far
JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600
I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.
View 5 Replies
View Related
Dec 24, 2013
explain me the Query Expression to remove the first 7 characters in a column, to rum a Query.Eg - I have entry like - 4/8/11-Pipe...I need to return the result like "Pipe".How to build the query.
View 5 Replies
View Related
Jun 4, 2013
I have a field called "SC" with a value 13-251. I need to remove the "-" ending up with 13251. I tried using, as a starting point, Mid(String, Start, Length) with no success. I found this in another topic, and thought this might be close to what I was looking for. I sure did not know how to use it. When I selected the run command I got the following response:
Query must have at least one destination.
I tried a few different things like adding an extra field and different pointers but I doubt if I'm even standing in the batters box.
View 3 Replies
View Related
Dec 2, 2014
I have a column named "stuffno" that i should enter the id of my stuff.I should enter "no 111".I did this because some stuff dont have id's so if i make a calculated field. "No " & [stuffno]I will get in the field of stuffno1 "no " for the stuff without id..Ok now i want to make another column that contains just the id of the stuff..Is there anyway to take just the id numbet the contents of "stuffno" field without "no " so the "stuffno1" will be empty or it will contain just the id.
View 3 Replies
View Related
Jul 2, 2015
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
View 3 Replies
View Related
Aug 28, 2013
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
View 3 Replies
View Related
Apr 26, 2013
I'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".
Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID
[code]...
View 6 Replies
View Related
Mar 1, 2015
I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.
I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.
I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.
View 14 Replies
View Related
Nov 28, 2013
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
View 4 Replies
View Related
Jun 4, 2013
How to query Select Distinct field + showing other fields? E.g.:
Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable
[Code] ......
View 7 Replies
View Related
Jun 11, 2015
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
.fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration
ORDER BY t_asset_personnel.LAST_NAME;
View 7 Replies
View Related
Apr 11, 2013
I'm new user converting over from Lotus Approach. I'm using Access 2013.
I have a database with two tables being Suppliers and Products. This is a one to many type database with Suppliers being the one table.
Both tables have their own data entry forms.
In the Products table and data entry form is field named Category with a value list of about 20 different values. The query I need to create is to search on the field value from one of the listed values in the associated combo box.
I have set up the basic query using the Query wizard. To run This I have to entry the value I wish to search for in the Query in Design mode.
My problem is now I want to drive the query from a new form. On the new form I have created a combo box where the field list is the same as on the product data entry form.
I need to use this combo box to select the Category field value I need to search for and then click on the button to run the query. I seem to be stuck on the SQL code to achieve this...
View 2 Replies
View Related
Sep 29, 2014
I am using this query to cluster a group of customers by monthly revenue. The issue is that I get duplicates. The customers appear more than once because the condition is valid more than once. How do I get access to select only once a field from Normalization sheet table??
SELECT [Cust Profile This Year].MSISDN, [Cust Profile This Year].[Total Revenue], [Normalization sheet].[Arpu Range] FROM [Normalization sheet], [Cust Profile This Year] WHERE ((([Cust Profile This Year].[Total Revenue])>=[Normalization sheet].[Arpu]));
View 3 Replies
View Related
May 16, 2013
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
View 12 Replies
View Related
Jun 26, 2014
I can not solve this problem, my Access knoledge is mediocre.
I have the following table from imported data:
RecNr IPaddres DateandTime
1 178.00.000.01 20-11-2013 21:47:21
2 178.00.000.01 20-11-2013 21:47:59
3 178.00.000.01 20-11-2013 21:48:35
4 178.00.000.01 22-11-2013 20:44:59
5 178.00.000.02 22-12-2013 19:47:59
6 178.00.000.02 22-12-2013 20:47:59
7 178.00.000.03 01-02-2014 12:47:59
8 178.00.000.03 01-02-2014 12:48:30
9 178.00.000.03 01-03-2014 11:47:59
etc
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
View 6 Replies
View Related
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View 3 Replies
View Related
Jun 16, 2015
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
View 1 Replies
View Related
Sep 23, 2014
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
View 1 Replies
View Related
Sep 16, 2004
I have a Date field, (an Expiry Date) and I want to use this in a Report that with Group the Report into Months. So we know, such and such a month, these are set to expire. Rather than have a separate Month column... how can I make use of exisiting Date in expiry field. Some one must know... Thanks in advance.
View 1 Replies
View Related
Sep 19, 2004
I am trying to remove a "sort and grouping" field from my report. I delete the field from the menu by using the "backspace" key on my key board. I am getting any error, invalid sort field. When I put something into the field, the error goes away. How do I delete a sorting field I do not want??
thanks for your help
newbie and learning
View 1 Replies
View Related
Oct 10, 2014
I have a database with a list of clients. One field is for their date of birth.
I have to report on equality and need to know how many clients we have within the following age groups:
16 - 24
25 - 34
35 - 44
45 - 54
55 - 64
65+
I have looked through the group and sort functions and cannot see anything obvious.
View 2 Replies
View Related
Mar 19, 2013
I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.
Using the following in the query design window.
Week of Year: Format([Date],"ww")
This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?
I tried sorting but that doesn't work.
I also tried the following:
Format([Date],"mmmm,ww")
But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3
Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.
View 5 Replies
View Related
Aug 28, 2007
I'm losing my mind on this one. Here is my situation... I receive a daily excel sheet with these fields. I would like to import this workfile into access and would like to manipulate it anyway I want. The problem I'm coming into is that I can't collapse/group the datetime field into just the date when I run a query. I would like to be able to run a query on any date range the client registered, a query on any and all the unique dates the client purchased something, all the unique clients, etc. Here is a sample
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/4/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/5/2007 18:20
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 01:22 - 8/9/2007 05:46
I would like it to show...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/5/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/6/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
and a different query like ( the purchase date doesn't matter here)...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
I read in other places to change the format, input mask, convert, char, etc. but nothing. Please tell me exactly what to do. I don't care if the time changes to midnight or 0:00:00. I will owe you my first born child.
View 6 Replies
View Related
Sep 12, 2013
I have one table in my access, and out of that I need to show a queri that will show only following result in 2 columns ID (same as original table)
SCHOOL_YEAR (all the students that are):
1 year and 1 year (repeat) as 1 year
2 year and 1 year (repeat) as 2 year
3 year and 1 year (repeat) as 3 year
Not to show dripouts, na and finished
Original table:
IDNAME
11 year
42 year
53 year
10dropout
11finished
12n/anot categorized
131 year (repeat)
142 year (repeat)
153 year (repeat)
View 3 Replies
View Related
Apr 1, 2014
Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?
View 2 Replies
View Related
