Queries :: Query To Select Part Or All Of A Table?
Nov 27, 2013
I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
View Replies
ADVERTISEMENT
Dec 14, 2013
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
View 1 Replies
View Related
Nov 30, 2013
Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).
row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013
I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.
View 1 Replies
View Related
Oct 26, 2014
I have generated two queries called "qry_GetWellName_GetAPI" and "qry_GetWellProduction" that extracts all the data I need from a csv to make two tables.
My issue is that I can not gather all the production data into a query formatted properly for a make table query that contains production history by month / api number.
Ideally the data would be placed in a table called tblProductionHistory with the following unique table index --> [api_fk] [data date]. The api_fk would link to the table that was created by extracting well names using qry_GetWellName_GetAPI.
tblProductionHistory field structure I am trying to achieve:
[api_fk]--[data date]--[oil rate]--[gas rate]--[water rate]--[days produced]--[cumulative oil]--[cumulative gas]
Currently the qry_GetWellProduction shows each well name and the year it had production as a separate record from the month names and the month's production values. I need to consolidate the data so each record can be matched to the unique table index.
Is there a sub query I need to use to manufacture a data date for each month that includes the year and last day of that month for that year? Also would I need a separate sub query to match each month's production to the api number?
View 1 Replies
View Related
Jan 21, 2014
I have made a new access 2013 database. I have created a linked table that has imported a substantial amount of data from an external data source, (an Excel spreadsheet). So far no problem. I created a select query that plucked data from the original table mentioned. Again, no problem. Then I decided to create another table, using certain fields only from the select query. Microsoft's guide tells me to start with CREATE, then Table design. I am happy to use just 4 fields from my query, but what I keep ending up with is a table, that, when I double click on it gives me the following:
ID Field1 Field2 Click to add
(New)
It is presumably expecting me to enter an ID number and it will come up with some record, but I want a complete table that should show several hundred records.
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
Apr 22, 2013
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
View 3 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 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
Sep 6, 2005
I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.
View 1 Replies
View Related
Dec 13, 2007
I have a table with 84000 records in I need to fined all the records that have the same First two starting characters and the same last two characters but are different between
the first two and last two characters.
Ie. 30123456757 302356757 301234567
I just want the ones that have different number in between . I have tries ever Select statement I can think of and still coming up with every thing but what I need.
View 7 Replies
View Related
Apr 4, 2013
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
View 2 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 29, 2005
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
View 3 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
Mar 20, 2013
are you able to specify the location of another database that you want to select data from?
For example: I want to do something like this:
SELECT [field1], [field2] from C:DesktopFolderMyOtherDatabase.mdbTableName
View 4 Replies
View Related
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Jul 10, 2013
Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-
Public Sub GetEquipment()
List387.RowSourceType = "Table/Query"
List387.RowSource = "SELECT findequipstr() FROM Equipment"
End Sub
Public Function findequipstr() As String
If IsNull(List371.Value) Then GoTo function_end
findequipstr = List371.Value
function_end:
End Function
If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged,
View 3 Replies
View Related
Jul 28, 2014
After you "group by", is there a way to have the next field presented without a sum or avg etc.. if I know there is only ever one value to present?
View 1 Replies
View Related
May 3, 2014
I'm having a problem get a query to select all of the records it should be.
When I filter the source table (200_STANDARDIZED NRGL) to show the data I want to see (PC2 = 6000; GAAP = 02; CGL = 0950, 2735, 2736, 3500 and 3501; STD VENDOR NAME = blanks), I get 33 records.
NOTE: Had to take the PC2 records that were not '6000' out of the dB I've attached in order to be able to send dB bu the PC2 filter is needed in the complete dB.
When I create a select query to the do the same thing, I get either:3 records (when I set STD VEND NAME to Like '') or 30 records (when I set STD VEND NAME to NOT Like '*' ) I've attached the dB - Query 1 is the subject of bullet 1 above and Query 2 is the subject of bullet 2 above.
BTW, the three records that show up in Query 1 are the same records missing from Query 2.
View 3 Replies
View Related
Aug 14, 2015
I Want to Create an Query to Find Different Values in A Table/ Query
Like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1
Feb 2012 Ankur School 1 Post 1
Mar 2012 Ankur School 2 Post 1
Apr 2012 Ankur School 2 Post 2
May 2012 Ankur School 2 Post 2
June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1 (Starting Ledger)
Mar 2012 Ankur School 2 Post 1 (Change in School)
Apr 2012 Ankur School 2 Post 2 ( Change in Post)
June 2012 Ankur School 3 Post 2 (Change in School)
View 1 Replies
View Related
Nov 15, 2013
How can set criteria in select query as Textbox1 or textbox 2 ...textbox 1 and textbox 2 are field on different forms.
View 4 Replies
View Related
Jan 29, 2015
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
View 14 Replies
View Related
Apr 23, 2014
Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
View 3 Replies
View Related
Jan 4, 2014
WinXPPro Sp3
Access 2007
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
List_Order
3
1
15
5
4
12
2
7
View 3 Replies
View Related
Jun 5, 2013
I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.
Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.
Private Sub SingleItemRptB_Click()
If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then
MsgBox "Item not in database. Please check value and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
End If
End Sub
View 4 Replies
View Related
