Queries :: Make Query Include All Records Even If No Data Found
Jul 10, 2014
I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff
Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
View Replies
ADVERTISEMENT
May 7, 2015
if its possible to have a button that after clicking it, it doesn't show certain records. Reason being I have a "shows" for each month on our personal site and amazon, and we've been selling on those for about 2 years, and I have a "show" for each month. But, that quickly clutters up the list box. Any way to create a button the (after typing in the name) it doesnt show only those record(s)?
View 2 Replies
View Related
Aug 5, 2015
I've created a query with the following SQL:
Code:
SELECT Table1.[Material Key], Table1.[Material Name], Table1.[Info]
FROM Table1 INNER JOIN search_MaterialKeys ON [Material Key] = search_MaterialKeys.[MaterialKeySearch];
So at the moment, users paste material numbers in the search_materialKeys table and the query is created with the necessary information. If the number they paste into the search_materialKeys table is not found in Table1 then the query doesnt return anything. I need the query to list all the entered values in the temptable in the query so that users know which of their inputs were not found. Is there any way to do this?
View 1 Replies
View Related
Aug 10, 2005
Hi
Each month I use Access to import text files and export cleansed data to Excel.
The information contains (i.e. forecast data) that has 6 months of data. Each month, the data is rolled over to include the new month and delete the oldest month of data.
Is there a query(s) or module that can automate this process?
Would greatly appreciate any tips on this one!!
Lucas
View 1 Replies
View Related
May 2, 2013
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
View 6 Replies
View Related
Sep 17, 2013
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code:
TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
View 5 Replies
View Related
Jul 4, 2013
I'm having a mental block on this one.
I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.
View 1 Replies
View Related
Aug 9, 2007
Hi
Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!
I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.
I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).
Hope that makes sense, please can someone do me an idiots guide?
Many thanks
Phil
View 5 Replies
View Related
Jul 10, 2015
How can I include a field from a table in to a query - that has no relationship?
For example...
The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...
"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement". Here's the current SQL statement
Code:
SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 21, 2013
I am receiving this error when i try to run a query:
"You tried to execute a query that doesn't include specified expression as an aggregated function"
There are two images attached which show the SQL view, the error and the design view.
View 11 Replies
View Related
Jul 28, 2014
How to create a message when no records are found from a form to a query in the report.
Example; Donations From Great Britain have no records
View 4 Replies
View Related
Jul 18, 2013
I have a search query that searches for different results based on 3 criterior. I have set up a form so that the user can input the text into the form and then once the Submit button (that i created) is clicked the query table will be shown.
My question is:
Is it possible to have an error message box appear when i click submit and no data is returned by the search query?
I'm hoping for the message box to say "No corresponding records to your search criteria. Do you want to try again?"
Then the options given in the message box are Yes (where they should be taken back to the search form) and No (Where they are taken to another form).
View 6 Replies
View Related
Jan 14, 2014
I am looking to make a table which adds a sequence number to my records.
Example data would look like this
Acc no Date
1234535 01/01/2013
1234535 05/01/2013
1234535 11/01/2013
9876543 02/01/2013
9876543 22/02/2013
9876543 01/03/2013
I want it to add a deq based on the account no then date in sequence oldest to newest.
Acc no Date Seq
1234535 01/01/2013 1
1234535 05/01/2013 2
1234535 11/01/2013 3
9876543 02/01/2013 1
9876543 22/02/2013 2
9876543 01/03/2013 3
View 5 Replies
View Related
Apr 17, 2013
How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
View 13 Replies
View Related
Nov 23, 2006
Hi everyone,
i am quite a begginer at this so sorry for the simple question
I am running a access query from a vba code. IN the vba code I am writing the sql command.
I am getting the criteria for the sql code (WHERE command) from a number of combo boxes in a form.
The whole thing works quite well when I have something selected on all combo boxes but it does collapse when one of the combo boxes has the default value which is "" nothing.
for instance when I have
WHERE domicile.IdDomicile = 5
works fine. 5 comes from the combo box
when there is nothing selected in my combo box the sql code would be
WHERE domicile.IdDomicile =
and then I get an error msg saying the query is wrong.
Question,
what is the command in sql that instruct the query to return all records.
I tried using "*" when the combo box was not selected but then I dont get the records that are blank, only the ones with something writen in it.
Any other suggestion for this
Many Thanks
K Regards
View 4 Replies
View Related
May 7, 2013
I have attached part of my database.
I need to show the complete rows for all the status in each year with or without any record/data.
View 5 Replies
View Related
Jun 27, 2013
I am trying to get the records on start and end date, still showing error no records found.
My code is like this:
Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"
[code]...
View 1 Replies
View Related
Apr 21, 2013
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
None of this works.
View 7 Replies
View Related
Aug 14, 2014
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
View 6 Replies
View Related
Oct 2, 2013
I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
View 1 Replies
View Related
Nov 20, 2007
Hi,
i've got a query that is linking 2 tables.
i'm having problems with the join properties.
basically from what i can understand, there are 3 options.
1. Where join field from both tables are equal
2. All records from table 1 and only those from table 2 where they match
3. All records from table 2 and only those from table 1 where they match
how do i go about having all records from both tables showing?
View 10 Replies
View Related
Nov 22, 2013
I have a query that's been working fine for years, however...the powers that be have requested a change. We need to track the lining owners in cars. We've recently gone to SQL on the back end (which I basically know NOTHING about) but I set up the new table, refreshed the connection, etc.
I can see the table fine and it says it has a primary key, auto numbers, etc. however, as you can see from the jpg below when I add the table to the query and link the id as in all the other tables, for some reason, this one makes all the data disappear when in datasheet view.If I delete the table from the query - it all comes back.
View 5 Replies
View Related
Sep 21, 2005
I am trying to make a query that will count how many times a word shows up in a field.
I am still new to access and am having trouble with this one.
Any help will be greatly appreciated.
Thanks in advance
Jon
View 1 Replies
View Related
Dec 3, 2013
I have a DB of books and a form to search for multiple fields including ranking (combo box). The way I have it now is to pick "3 stars", "4 stars", "5 stars". What I want to do is have it so that if I pick "4+", it will show all records that have 4 stars and 5 stars and "3+", it will show records with 3, 4, 5 stars.
View 14 Replies
View Related
Mar 15, 2008
I have a form which contains a listbox. It is a multi-select listbox. And I have a button on the form which runs a report. But I want the report to only show the selected records from the listbox. The report gets its records from an underlying query. But how do I make the selected records on the listbox become the query criteria?
View 4 Replies
View Related
