Query To Show Show Different Results From Same Group
Jun 11, 2006
I wondered if someone could help.
I have 3 columns of data within a table and from a select query am trying to seperate certain groups.
Table 1
yr period valuehome
2006 0 100
2006 0 10
2006 1 1000
2006 1 800
2006 2 60
2006 2 50
2006 3 40
2006 3 10
2006 3 5
2006 3 70
2006 4 50
2006 4 5
I wish to show both results for period 0 and an accumulative total for periods 1-4
so results would be
year period Total sum
2006 0 110
2006 1-4 2090
Can anyone help?
thanks
Paul
View Replies
ADVERTISEMENT
Jul 5, 2005
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
View 10 Replies
View Related
Jan 24, 2006
I have a form that I am trying to show in a text box the results of a count query. The query has two fields in which one is a check box. The criteria for the text box field is set to true to list all that are checked. I want the query to show all the records that are true. This works if I run the query, but the result isn't showing in my text box on the form. On the form text box properties in Control source I have =DLookUp("[Run Number]","Control Room Drumctqry"). Run Number is the field that I am counting, Control Room Drumctqry is the query. Any idea as to what I am doing wrong?
Thanks,
View 7 Replies
View Related
Jan 24, 2006
I have a form that I am trying to show in a text box the results of a count query. The query has two fields in which one is a check box. The criteria for the text box field is set to true to list all that are checked. I want the query to show all the records that are true. This works if I run the query, but the result isn't showing in my text box on the form. On the form text box properties in Control source I have =DLookUp("[Run Number]","Control Room Drumctqry"). Run Number is the field that I am counting, Control Room Drumctqry is the query. Any idea as to what I am doing wrong?
Thanks,
View 1 Replies
View Related
Apr 14, 2015
I've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
View 4 Replies
View Related
Jan 16, 2015
I have a query that is search for fields in a table that are either
1 - High
2 - Medium
3 - Low
I have a query that counts these and then puts the results into pie charts on a report.
However, when there is no "1 - High" value in the table against a paricualr criteria, obviously the quiery has nothing to look for an does not show a 0 value against the criteria but simply omits it (correctly) form the results.
This does affect the pie charts though which I want to show red for High, yellow for Medium and green for Low.
Therefore I need the query to show all criteria search results include 0 values, or to understand how I can colour code the series rather than the segments on the report.
View 1 Replies
View Related
Jun 28, 2015
I basically want to Loop through a query (or if not possible trough a table) and show the results in my form. For each record in the table I am doing additional checks when loading the form and showing that result accordingly. I do not want to do these checks in additional queries - I think it is more efficient doing it in the form directly. Here is the code I have:
Dim dbsSR As DAO.Database
Dim rstValQry As DAO.Recordset
Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
With rstValQry
While (Not .EOF)
[Code] ....
The Loop seems to work because in debug it is going through it 3 times (the number of records I have in the query), but the result is always the same - it seems the above code is not checking / refreshing based on the individual query records.
View 14 Replies
View Related
Feb 27, 2014
I created a search form. It has 6 unbound text boxes and 2 combo boxes in the header. Users can select values from the combo boxes and/or enter names in the other text boxes. These values all go into a filter on my Main table and the filtered results show up in the detail section. That used to work fine.
Now, I've been trying to convert the filter into a query and show the query results in the detail section instead.
(Why? Because of the ever-changing business requirements, of course!)
For some reason, the detail section went blank. All white. When I change the Data Entry property to No, it fixes that.
However, the text boxes for entering the search criteria will not accept any values anymore. It's like they are disabled.
When I change Data Entry property to Yes, I can enter text into the text boxes again. But the detail section blanks out again.
Windows 7 Professional, MS Access 2007/2010
View 2 Replies
View Related
Dec 4, 2014
We have a button running a SQL query via VBA, how can I make it so the results doesn't show up in a table/preview?Also, I know it's for the SQL forum but how can I make a text box to show the results of a query to display it on screen?
View 8 Replies
View Related
May 3, 2015
I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.
All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :
Code:
SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName
FROM ItemBatch
WHERE (((ItemBatch.Finished)=False)
AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));
What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.
Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
View 3 Replies
View Related
Feb 11, 2006
I'm trying to view the last two valuations for each property so that they can be compared. I can pull out the last valuation for each property and can pull out the top two records but not for each property, just shows me the top 2 of all records.
Any suggestions?
View 5 Replies
View Related
Jan 4, 2005
dear frens,
hope some1 can pls kindly help....
SQL access:
SELECT tbl1.dat1, tbl1.Type, Count(tbl1.ID) AS CountOfID
FROM tbl1
GROUP BY tbl1.dat1, tbl1.Type;
result:
=========
12/12/2004 type1 20
12/12/2004 type2 13
12/12/2004 type3 11
20/12/2004 type1 4
20/12/2004 type2 6
25/12/2004 type1 33
25/12/2004 type3 21
how can i get result like:
=======================
12/12/2004 type1 20
12/12/2004 type2 13
12/12/2004 type3 11
20/12/2004 type1 4
20/12/2004 type2 6
20/12/2004 type3 0
25/12/2004 type1 33
25/12/2004 type2 0
25/12/2004 type3 21
which will show all the type even the count of ID is 0.
can any1 pls kindy help....thanks
snowbelly.
View 3 Replies
View Related
Oct 19, 2006
I have a query based on a table that has the fields as shown below. When I run the query, I will get a separate record for each group as shown below.
prb_no | seq_no |commitment_user_group_cd
602 |3 |ERC
602 |3 |MCE
602 |3 |SRG
602 |4 |MCE
602 |4 |SRG
602 |5 |SRG
602 |6 |MCE
I would like to use a query to show only the records where "SRG" is not listed and where there is another group. So in the example data above, I would like the only record to show in the query to be the last record because it's the only one where SRG is not listed along with another group.
The logic behind this is when a problem is initiated and a group assigned, we do some things and document the results. So I am only interested in the records where we have not documented our results too.
Does anyone know how I can do this with a query?
Thanks,
Jim
I want the query to only show the data where there is a user group
View 6 Replies
View Related
Jan 23, 2007
Hello All,
I am having problems getting the following lines to do what I need exactly. The formatting is kicking my butt basically.
Bob helped with these lines but I actually need the result to be last month. For instance it is now Jan. 07 and I need the query to total up the hours flown for Dec. 06. On Feb 1 07 I need it to now display Jan 07 totals and so on.
Query Field:
Format$([tblEntry].[tblDate],'mmmm yyyy')
Criteria:
Format(date(),"mmmm yyyy")
How can I change this to do what I stated above?
View 3 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
Mar 18, 2008
Hey,
I've got to make a query that displays all records whose Date/Time field appear after another tables Date/Time field.
I suppose I could say this is for use in an "Item scanned into inventory, Item Scanned out of Inventory" fashion.
To purpose is to use the total items returned after the date/time to be subtracted from the total of items that were scanned in at an earlier date.
I always tend to get redundant because I'm never sure how well I explain something, it would work something like such:
Table 1(Items Out):
ItemID | DateTime | Quantity
1 1/1/08 1:00 1
1 1/1/08 3:00 1
Table 2(Items In):
ItemID | DateTime | Quantity
1 1/1/03 2:00 1
So effectively the query would run, with the date/time criteria being ItemID 1's Date/Time. It would ignore the 1:00 entry as it occured before the first Item that was scanned in, and return that we have 0 ItemID 1s in inventory.
Inventory is just being implemented it was seat of your pants in the past that is why I have this dilemma we dont know how many are in stock until we check it manually but we know how much has left. This is why data before our manual check is not important to our current totals. We could have sent out 5 ItemID 1s but only have 1 in stock now, and if we use that data we would have -4 in inventory. I'm attempting to use the oldest date/time of the manual scan (table 2) as a criteria per item scanned against table 1. Hopefully, to return the present total of items on hand.
Edit*
As a note, I understand how to use >=#1/1/08 1:00:00# as a criteria but for some reason I cannot swing using a field in a table as criteria. That's pretty much what I can't find on the board or in my books.
View 1 Replies
View Related
Nov 20, 2006
Hi,
I have a list-box(Category) and a 'ok' button on the form.
My OK button has the following [Event Procedure]:
Private Sub OK_Click()
Me.Visible = False
DoCmd.RunSQL "Select [Barg Unit],[Medical Option],[Medical Coverage Tier] FROM RetireeCensus Group By [" & Category & "];"
End Sub
First of all, is my syntax correct in the above codes?
Second, what VBScript codes do I need to add in so that when I click the 'OK' button, a report or a table will show the query results from my 'Select' statement?
Thank you.
View 2 Replies
View Related
Apr 2, 2014
1.
i create a form and i want do do the searching in it so i but a button and combo box and the list box to show the results and the searching is from query
my form name is form2 and the button name is search and the combobox name is combo212
2.
how can i show the total in the charts in access like i create a chart for students and years so every year i know how many student in and i want to know how to sum all the student in all the years ans show it in the chart?
3.
i create a tow search combobox and button the question is how can i siprait the searching from the tow combobox like i search for the interest in the first combobox and the seacond one is for the years but i want to search for the interest in all years i tray it but it sis not work with me so how i can do it
4.
how can i add data to the combobox without going to the table ?
View 1 Replies
View Related
Jan 31, 2013
I have a small school with number of teachers, every teacher has a specific number of observations in the semester, i need a small project that when i query a bout a teacher, a form appearing with his name and the remaining hours of observation
what i did is i made two tables
teacher (id, name, total number of observations in semester)
observation( Oid, name of teacher, date and time of observation, remaining observations)
I dont know if i have to make a counter or what !!-- Now what i need to do is a form when i enter the id of the teacher, it shows automatically the name of the teacher, and the number of remaining observations
How can i join the two tables and show the results ?
View 2 Replies
View Related
May 30, 2013
I have a list box that is fed by a query. I have fields that are stored in the table as numbers (Type is Double and Standard Number with 3 decimal places).
In the tables I see the numbers 10.000 which is correct, in the query is see 10.000 again correct, however the list box on the form only shows 10.00?
View 3 Replies
View Related
Jul 5, 2014
I am needing a fairly simple query to show records from a table that occurred between a set of dates specified by the user; I have quickly built the query using the wizard, however the date is stored as DateTime;
In the criteria, I tried - Between [Enter Start Date:] And [Enter End Date:] which returns an empty result (presumably because there are no records that match exactly, as they all have the time included?)
Is there a way to have the query show all results between the dates specified by the user, regardless of the time?
View 7 Replies
View Related
Aug 4, 2013
I have multiple tables (Desktops and Telephones)
A search form, to search into those tables (It searches by "User")
The search form contains a listbox that shows results (listPC)
And the following code:
Option Compare Database
Dim strUserPC As String, strUserTel As String
Dim db As DAO.Database, rsUserPC As DAO.Recordset ', rsUserTel As DAO.Recordset
Private Sub txtSearch_LostFocus()
strUserPC = ""
strUserTel = ""
[Code] .....
It works, but I have one problem. It only shows telephones or desktop, not both. It deppends on which line inside UpdateList is first.
That example searches into 2 tables:
-Telephones
-Desktops
And searches by "user".
I want to list all telephones and desktops that a user has assigned.
What should I change to show both results?
View 9 Replies
View Related
Oct 16, 2014
I have an unbound calculated field [Txt1] that I would like to show the result as currency. The fields in the calculation are:
[CR] number, currency
[Commission1] general number, two decimal places
[Commission2] general number, two decimal places
The calculation is: CR15W * (1 - Commission1) * (1 - Commission2)
If I just set the field to currency it works fine. But I want the field to show the following:
"Some Text" & " " & Format(Calculation, "Currency")
When I tried to include the format it to currency I get type mismatch, run time error 13.
So I thought that perhaps I should try the following just to see where my error starts and I get the same error message when I use:
Dim Val1 As String
Val1 = CR15W * (1 - Commission1) * (1 - Commission2)
Txt1 = Format(Val1, "Currency")
Everything I have read says the formula is:
Format([Calculation], "Currency")
Is correct. So why won't it work for me?
View 8 Replies
View Related
Jul 28, 2015
Lets say I have the follow Tables:
Code:
Outfits:
ID | Top Color ID | Bottom Color ID
1 2 1
2 3 4
Colors:
ID | Name
1 Red
2 Blue
3 Green
4 Orange
I'd like to have a query in design view to have the following result
Code:
Outfit ID | Top Color Name | Bottom Color Name
1 Blue Red
2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
View 4 Replies
View Related
Jun 7, 2007
Is it possible to convert fields listed down to show across.
IDNo Code Position
AAA B01 1
AAA C01 2
AAA D01 3
AAA E01 4
I want to show it as
IDNo Code Code Code Code
AAA B01 C01 D01 E01
Is this possible using Access 2003?
View 7 Replies
View Related
Mar 26, 2014
Access 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
Code: txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")
In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form
What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!
example
entry1
entry2
entry3
entry4
View 7 Replies
View Related
