I am trying to make a query that counts the orders on a day of an specific month selected from a form.
The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.
Any way of making a query that adds the days with no records with a count value of 0?
I try this code
Code:
SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot,
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg,
Round(Avg(ReportTbl.avg_len),2) AS LenAvg,
Round(Avg(ReportTbl.avg_in),2) AS InAvg,
Round(Avg(ReportTbl.avg_out),2) AS OutAvg,
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg,
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY ReportTbl.lot_date;
But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"
and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",R eportTbl.lot_date)
or other code that I can search in google
I get the warning message that resemble with above message
and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.
I have a table (tbl_entry) of performers and the different sections they are performing in. I can pull the performers for each individual section but is it possible to then give the performers a random number that will indicate the order in which each will perform. Eg: 5 performers in section 21a
Molly
Mary
Mike
Merv
Mandy
So can I get access to randomly assign numbers 1-5
I have a table with RandomID, EmpID, CompanyID. Some Companies will have great than 25 employees. I need to setup a query where I can randomly select all employees from all companies whos employee count is less than 25.
I have tried the following:
SELECT top 10 percent Count(*), empID
FROM tlbCompany
Group By empID
HAVING Count(*)<25
ORDER BY rnd(RandomID);
This counts the total number of employees, so if a company has 10 employees, it will only display one of the them.
Can this query be done?
And, what am I doing wrong?
Thanks to all that help....
Enviva..
my database was working fine until a new month was added in the attendance now each staff member has about 20 records when they should only have two. if i remove the second month it corrects its self but i can't do that permanently also i have office 2010 but the office has 2003 so i saved it to 2002-2003 file format and it was working fine up until today when it has started to give and error message Which Reads: Your Microsoft Office Access database or project contains a missing or broken reference to the file 'ACEDAO.DLL' version 12.0
View 6 Replies View RelatedI 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.
I'm not sure why I can't think of how to fix this, but I'm missing something
I have a table with a column for 'birthdays' The format is mm/dd, and a form for the users to put in their birthday.
When you look at the table, it may look like 05/12. But if you click on the cell it's shows it as 05/12/2007.
The problem I'm running into, I have a Data Access Page that displays all the birthdays grouped by month. But for some reason all the entries from 2006 are grouped by month, but then all the entries we've made in 2007 are grouped together by themselves.
so we have
(all the entries we made in 2006)
June
July
etc
but then at the end of the list
we have again
June
July
etc (these are the ones we entered in 2007)
Is there a way to store a date as only mm/dd?
Or is there a way to make the DAP group these by month, and ignore the year?
Hi guys.
Can anyone tell me how to group the results of a query by month in a date field?
MonthName(DatePart("m",[Date])) ... this gives me the month-name but I cannot order the results. I'd like smth like 01 (for Jan), 02 (for Feb) ect.
so from a date I need to get only the month.
Thanks in advance.
I have a table column with dates like
02/05/06
02/06/06
...........
I need to calculate the next date[Due Date] in 6 months, which is simple
[Patient's file].[Last Appointment Date]+180 as [Due Date]
and I have to group the Due Date by the next month. Lets say the due date is 08/25/07, so it has to be grouped as September group at the top or the list. It should be displayed as "September, 2007"
How should I assing let say 08/01/07 - 08/31/07 dates to September groups. I mean the statement should be universal for any day and any month of the year.
Thank you.
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
=Int(DateDiff("d", Date1, Date2) / 7)
but this does not work for the 5 week months?
Any help would be greatly appreciated.
i need 2 comboxes. 1 with year and 1 with month. the year is easy just do this in rowsource sql:
SELECT DISTINCT (DatePart('yyyy',uitvoering.begindatumtijd)) AS Jaar
FROM uitvoering;
but the month is a lot harder because it needs to show the months where the year is the same as the combox with year and only show months where there is data in my table. for example i have data for only 3/12 months in a year then i need to show only those 3 months.i made this in vba because i think it can't be done in rowsource with sql code.
Private Sub Tekst19_Click()
Dim year As Integer
year = Me.Tekst17.Value
CurrentProject.Connection.Execute "SELECT DISTINCT (DatePart('m',uitvoering.begindatumtijd,,)) AS Maand FROM uitvoering WHERE (DatePart('YYYY', uitvoering.begindatumtijd)) = year"
End Sub
OK
I have read through I think every post with the word email in the title ! and in the subject... and tried various bits of code that were posted, but can't get anything to work... :mad:
Here is what I want to do.
I have a form (based on a qry called "QryInvoices Due") which brings up a list of people.
On this form is a field called "send email?" which is a tick box which the user can select.
I also have a command button ! which When Clicked I want it to do the following.
Send an email to all of the recipients which have "send email?" as YES
their email addr is in a field called "Contact email"
I don't want the email as an attachment, I don't need the message or even the subject included (although wld be nice as will be fairly static i think :) )
All I want is it to bring me up the Outlook New Message with all the recipients in it, I can add the subject and message then if necessary. Oh and there's more !! ;) I need the recipients to be in the bcc field as I don't want them all to see each other, the to: field can be blank or the first recipient whatever.
Anybody got any code that I can just copy and paste !! and that will miracleously work (sp?) ... Hope u can follow what I;m looking for..
Many thanks in advance
D
Hi,
On my form I have an option group with 4 radio buttons. Depending on the button selected certain fields become visible. This works all fine. Problem is though that I don't know how to store the selected value. At the moment, every time you open the form you have to select the same button to see the info stored in the fields that become visible. I guess that I'll have to somehow store the value for the selected buttons in the table. But How? Do I refer to the frame and store the value corresponding to the number of button (1, 2, 3 or 4) or do I store the balue for each button seperately?
I've played with it for too long now and cannot find the golden tip. Please advice.
Thanks,
Walt
Hi All.
In a query I grouped DATE by month Date:Format([Date],"yyyy mmm") to calculate visits in each month. My problem is: How to create dialog where user can enter date based only on YEAR and MONTH and enter in this dialog start_date and end_date?
Thanks.
I would just like to know if it is possible to group Months together: this is what i have did so far :
Code:
SELECT Count(Source.ID) AS CountOfID, Clusters.Cluster_Desc, Department.Dept_Desc, DatePart('m',Source.Day_Month_Year) AS [Month], Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action
FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID
GROUP BY Clusters.Cluster_Desc, Department.Dept_Desc, Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action, Source.Day_Month_Year;
I'm developing a database where people basically can fill in an order form and then the database can track if the order has been placed, when it came in etc.I have a form called frm_Suppliers to be Ordered that runs off a query. The tables that relate to the query are called tbl_Products, with a primary key called ID and tbl_Suppliers, with a primary key called Supplier ID.
The form called frm_Suppliers to be Ordered, shows a list of suppliers that have open orders. Each supplier may have several orders open. I want to be able to click a button after each supplier name and bring up a continuous form that relates to all the records from that supplier that have open orders.
I have tried using the code found in the thread called "open a form based on the record that is selected in current form" from 06-08-2011, however it will only work when my form lists each open order separately, not when they are grouped by supplier.
Hello, all.
I posted this about a month ago, but at that time I was running myself ragged and through too many problems at once. I stepped back and made some good progess. I put this in the General forum because it could encompass VBA, queries, and reports
I have a main report (Percentage Report) that has 4 subreports in it. Each subreport is based on a query that's run from three other queries. Its a neatly tangled mess, but it works fine.
The queries all count and calculate percentages for a pass rate of inspections on maintenance. There's an over-all/basic percentage that simply totals everything and divides for a percentage. There's also a "maintenance" percentage that only takes into account inspections done on maintenance (as opposed to various programs and processes.) Those both work fine for any given time period.
The third (and final) percentage deducts 0.5 points for each of a specific list of inspections (safety and other violations.) This works fine so long as you're only looking at a month's worth of data. The problem comes when you want to view any time period larger than that (quarter, semi-annual, annual.)
Basically, you end up subtracting a sum from an average and you end up w/ totally inaccurate numbers. I just can't quite figure out how to effectively either group by month or how to average the deductions based on the months covered.
I just finished completing this whole thing, and I'm pretty much done for tonight. Any help would be great.
----------------------------------------
Key words: sum totals, report grouping, report conditional format, alternate row colors (greenbar), count, calculate, percent
I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
View 14 Replies View RelatedI am trying to print selected query how can I do that what is the command for that ...
View 1 Replies View RelatedI have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
View 2 Replies View RelatedI have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
View 1 Replies View RelatedI have a Union Query which the result was like this:
Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00
Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date.
I have this Access file with Tables & Queries.
I want selected queries to appear in RED colour and if possible BOLD too.
Possible ?
I want to limit the results in a query to the selected items on a list box - how do I do this?
View 4 Replies View RelatedI am trying to query any date before current month. My data is employee start dates, thus my end aim is to pick up all employees at end of last month.
View 1 Replies View Related