Does anyone know how to do a query so that the user can find all the things that will expiry at the end of the current month?
The user will click on a command button and it will show the results of a product that will expiry at the end of a current month.
Have tried with parameters to which the user manually types the end date in and then it will show the results but having problems.
Cheers
SELECT Training.ID, Training.StaffPIN, Training.Module, Training.DatePassed, Training.DateExp, Training.Comments
FROM TrainingModules INNER JOIN Training ON TrainingModules.[TrngModule] = Training.[Module]
WHERE (((Training.StaffPIN)=[Forms]![TrainingFrm]![StaffPIN]));
Does anyone know why this query isn't working? It doesn't throw an error, it's just not showing any records.
The only thing I can think of is:
Training.StaffPIN is a long int
When it prompts me for [Forms]![TrainingFrm]![StaffPIN] I enter '12177' in the message box, is that taken to be an int or is it actually a text string?
It might not be this as when I use the form [TrainingFrm] to pass the perameter, it's taken from a combobox ([StaffPIN]) where it IS an int, and the query is still blank. :confused:
TrainingModules is a table that stores all the possible modules or subjects that a member of staff can be trained in
Training is a table that stores which staff member has passed which module or subject.
I want the query to pull all the info on a staff member from Training to be later used as a subform.
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
I am fairly new to access, but do have some experience with SQL & PHP.
I am trying to do something which I would have thought was quite straight forward. But I keep hitting a brick wall and all I get is #name? error on my form.
I have a table "tbl_Job_Spec" and a table "tbl_Tool_List".
I would like to have on the form for tbl_Job_Spec a drop down for selecting the tool number from the "tbl_Tool_List", which then automatically puts into a text box on the form the tools location, which is stored alongside the Tool Number Field on tbl_Tool_List.
I've tried loads of things, I have a query setup ("qry_tool1loc") which seems to correctley identify the relevant information, but when I try to reference that query on the form if just comes up with #Name? in the box on the form.
Anybody got any ideas? I'm only at an early stage in what is quite a big project, but to be honest this is about as complicated as it's going to get, so once I can get past this hurdle it should be plain sailing!!!!!
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
I am trying to use this query. It gives me correct results as query. However when I make chart with query on a report it doesn't show correct data and eventually stops making chart
SELECT qry.txtRC, Count(tbl.txtRC) AS CountOftxtRC
FROM tblMain AS tbl, qryRC AS qry
WHERE (((tbl.txtDepartment)=[Forms]![frmRC]![cboDepartment] Or [Forms]![frmRC]![cboDepartment] Is Null) AND ((tbl.txtZone)=[Forms]![frmRC]![cboZone] Or [Forms]![frmRC]![cboZone] Is Null) AND ((tbl.txtRC )=[qry].[atnRC ID]) AND ((tbl.date) Between [Forms]![frmRC]![startDate] And [Forms]![frmRC]![endDate]))
GROUP BY qry.txtRC , tbl.txtRC
HAVING (((Count(tbl.txtRC )) Is Not Null)) OR (((Count(tbl.txtRC )) Is Not Null))
ORDER BY Count(tbl.txtRC ) DESC;
Access 2010. This has worked before but I don't understand why this is not working for me now. When i select an item in a combo box in a form and click on a button to run a query with the results, the query is blank. If I run the query alone, it prompts for an item, I can type it in and it works. I have even tried the DoCmd on the combo box but still same results. Attached is a dummy down DB. If you run the query, it will prompt, select Paper or Rock, see results. But run the form, the drop down will not show the results. What am I missing?
View 3 Replies View RelatedI have two database tables, one of information of members and one of staff. I want to do a query to show all members and staff that have the title of 'Mr' in the database.
There are only 5 staff details in my staff database at the moment, all with the title 'Mr'. There are 20 members details, 13 with the title 'Mr'. When i run the query i get only 8 names, 4 from the staff and 4 from the members.
The 4 staff ones i get are the 2nd, 3rd, 4th and 4th records.
The first record of the members field does not have the title 'Mr' and so it appears that this is blocking the first staff one to appear. And the members stop appearing after the 5th record too, so it appears they are stopped as there are no more staff records.
I hope this makes some sense to you, and if anyone could tell me how to get all the records to appear that would be great. Thanks.
Hi
I have a query which is made up of several other queries that total different fields (these are a base for a report) sometimes one of the queries will be empty and then the whole query is blank, how can I return 0 when there are no records to count in 1 query so that the results from the others still show?
Thanks
I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?
View 9 Replies View RelatedI have googled and search for this subject and am more confused now...
I am running an offline A03 db with XP and have a hosted website. Right now I have a separate db online that is used for the results on the website, and it works correctly. I used frontpage to create the form.
Now with our offline db I would like to enter the information on a form, and have the results show up on our website, to avoid keying the same info multiple times.
The data that shows up on the website will not be edited, but the table offline will have some fields for edit, that will not show up online.
Is there a way to connect an offline table/query to show up as results online? And if so how or examples? Thanks in advance.
I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description
CarNum
SerialNum
Category
Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.
To cut a long story short, i've got a union query that is a full outer join of 2 tables.
Anyway, the results i have got back needed formatting to no decimal places.
How do i do that?
I've tried leaving it as it is and formatting it in the report, but it still doesn't format and i can't total it up.
It's like it is showing as a text and not number.
Is there a way of converting the result to a number, particularly in a sql query.
I have a tabular form which i use to input new payments for tenants.
What i'd like to be able to do, is have another form, with a combo box that i can chose my tenant from, then for a tabular form to show only the payments made for that tenant, sorted in date order.
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
Hello
I have a problem that is really giving me great headaches. I have had no success what so ever. My gratitude goes to anyone that can provide some insight.
I have two tables. One table for appointments and one table of all available time slots (ie 0900 is record 1, 0930 is record 2, 1000 is record 3 and so on).
If lets say there are records in the appointments table for eg today 01/06/2005 where there are two appointments (eg 0930 and 1000) I want to build a query that shows all the appointments that have not been used (so 0900, 1030, 1100,and son on). Adding to this, I need it to show these available timeslots for multiple dats (as the appointments table will have some appointments booked for the 01/06/05 and some for the 02/06/05 and some for eg 10/06/05).
I am aiming to implement this on a Active server page where the user selects the data and then the available appointments are displayed for that given date. However, I know that the query as descibed above is essential for this to be developed!
I have spent many hours trying to solve this with absolutely no success, so many thanks in advance to anyone that can help. Apologies for descibing the problem thoroughly.
Thanks
Ricky.
Below is the code I am using in a query that produces results that look like:
Fault Category------------No Fault Totals
No Faults-----------------------77
Total Work Units---------------521
I would like to also show in the query the percentage of No Faults. In this instance I would need to divide
the 77 no faults by the 521 total work units. Is there a way to do this and show the answer in the query.
I do not want this in a report, just the query.
SELECT FaultCategory, COUNT(*) As [No Fault Totals]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
FaultCategory IN ('No Faults') AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]
GROUP BY FaultCategory
UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals]
FROM (Select Distinct [WorkUnit]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;
Hello. This is a very basic question. I have a query that has relationships set within. Now.. When I pull up the query with a criteria such as "date" ... the query pulls up all the information for only the items that have all the related fields filled in. How would i make this query show ALL the items from that "date" even if their related fields are blank or there are no relationships in another table?
Please let me know.
OvAdoggvO
Below is the code I am using in a query that produces results that look like:
Fault Category-------No Fault Totals
No Faults-------------------77
Total Work Units-----------521
I would like to also show in the query the percentage of No Faults. In this instance I would need to divide
the 77 no faults by the 521 total work units. Is there a way to do this and show the answer in the query.
I do not want this in a report, just the query.
Code:SELECT FaultCategory, COUNT(*) As [No Fault Totals] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND FaultCategory IN ('No Faults') AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt] GROUP BY FaultCategory UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals] FROM (Select Distinct [WorkUnit] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;
Hi All
I have a feeling that this is an absolute newbie question.
I have three queries, qryVisitsDue, qryVisitsOverDue, qrySiteView.
The query qrySiteView uses SiteID to produce a listing of all sites which includes a count of visits for those sites. If there is no visit data the site does not display. To display the counts I'm using the other two queries. In the first two I'm querying a table, tblVisits, to find what visits are due or overdue based on the current date. They work fine where there is visit data. My problems start when there is no data for a site. I need to be able to show the sites where there is no visit data.
I'm not very experienced so basic explanations are probably the best.
Any help much appreciated.
ChrisD
I want to use a column in a query to show where data is missing in other fields.
In excel I have used this statement:
=IF(COUNTA(I5:J5)=2,"","error")
Basically, I have two fields PRICE and WEIGHT. I want a column in the query to show 'Error' (or any kind of flag) when either (or both) of these fields are blank.
Hope this makes sence.
Any advice?
Thanks
I have two tables, bad design, not my choice of the way it's set up.
Table a has Fields 1-2, dups allowed in all fields. No pk was originally assigned.
Table b also has the same fields 1-6, but also has field 6.
Table a has all of the records in question, table b only has some of the records from table a but does not have any records that are not in a.
Something like this.
Table a
Field1 Field 2 Field 3 Field 4 Field 5
John Shirt blue denim button
Mark pants green straight
John Shirt blue silk button
Table b
Field1 Field 2 Field 3 Field 4 Field 5 Field6
John Shirt blue denim button new
Mark pants green straight used
If I query the two tables, a left join to b, then I get the correct number of total records. If I select any records from b, then the total records goes higher than there are, even for the two combined, listing the same record multiple time.
Any suggestions are appreciated. I am a blood bank lab person, not an IT person, or DBA, so simple responses are greatly appreciated.
I have make relational table on query, but some record not sohowing on query, although i have the record on table.
for example, i can't see the record with code :
131401.007
541000.00*
is the anything wrong?
thanks
Is it possib;e to create a query that would all the dates withn a range of Date1 and Date ?
Ex if I have
Filed Date1 Date2
x 1/1/07 1/3/07
c 1/6/7 1/8/7
for query to show
Field Date
x 1/1/7
x 1/2/7
x 1/3/7
c 1/6/7
c 1/7/7
c 1/8/7