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
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.
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.
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.
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 want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.
I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?
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 database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items
I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.
After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.
I have a table (table1)that table has several columns: company / Qty / date / ERT...I want to be able to run a query that will group all the records by Company and by date and provide the sum of QTY - as well as the cumulative totals for each company and date.
My query looks like this: amonth: MONTH CODE Group By assending
Field 1: Alias: Company Group by
Ascending
Field 2: QTY Table1 Sum
Field 3: Runtot: DSum("qty","table1","[month code]=" & [amonth] & " And [company]=" & [alias] & "")..I have the query set up - but my runtotal Dsum formula is giving me grief - specifically a #ERROR and I can't figure it out..I basically want it to look like this:
A company 15/Nov/2012 10 10 A Company 16/Nov/2012 10 20 B Company 12/Jan/2012 15 15 B Company 21/Mar/2012 5 20
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.
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
I need to output an HTML table with the following headings:
============================================== Event Title | Event Price | No. Delegates Booked | Total Price ==============================================
How do I group(/join?) the tables to show this data? I'm confident with doing the recordset stuff, it's just that I have no idea how to build a SELECT string, with data grouped into events where the total number of delegates who've booked on that event will be added together.
I have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.
PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));
I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.
SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));
On the form I have an Option Group control that sets the visible properties of the 4 controls; 12-Month Rolling cboMonth - Month Combobox txtYear - User entered year Date Range txtStartDate - User entered Start Date txtEndDate - User entered End Date
What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly
If blnDateRange Then Me.txtQryStart = Me.txtStartDate Me.txtQryEnd = Me.txtEndDate End If
If blnMonth Then If Me.cboMonth = 12 Then EndMonth = 1 EndYear = Me.txtYear + 1 StartMonth = EndMonth StartYear = Me.txtYear Else EndMonth = Me.cboMonth + 1 EndYear = Me.txtYear StartMonth = EndMonth + 1 StartYear = Me.txtYear - 1 End If Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1 Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1) End If
Now I can use one query to accomplish both types of query; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));
This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.
What I was wondering is there an better/simpler way to do this? :confused:
I am using the following code to get results from next month,
Code:
Month([ptdate])=Month(Now())+1
which works great to get next months results. I try to get what is left of this month by adding the code:
Code: Between Now() And Month([ptdate])=Month(Now())+1
This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.
We're developing a database to manage tournament registrants as well as event results.
The key tables we have ar: ContactsRegistrationsRegistration DetailsTournamentsAgeGroupsBreakingAgeGroupsExperienceGroupsEventsPayments
There are queries for using information in the tables to determine age, competition divisions etc.
At the moment the RegistrationExtended query, which uses the ContactsExtended and TournamentsExtended queries and the Registrations table to determine the number of events for each registrant as well as what division and age group they are assigned. This is done with some IIF expressions.
For the 2012 event we had 4 division classes based on experience and 6 age groups. For 2013 there might be more division classes and age groups. So now I have the tables AgeGroups, BreakingAgeGroups, and ExperienceGroups. The two age group tables have a query that calculates the age group name based on the values in the MinAge and MaxAge fields. For example 8 & Under, 9-11, 12-14 etc. The ExperienceGroups also have a minimum and a maximum field but only one field will be completed for each experience level. If the value is a maximum then that would be for those with less than the maximum experience and if the value was a minimum then that would be for those with minimum of that many years of experience. For example Grasshopper for less than 1 yearWarrior for those with 1 year to less than 2.5 yearsSamurai for those with 2.5 years and less than 4 yearsShogun for those with 4 or more years.
We want to maintain the history so the age groups and experience groups are now tied to the tournament.
I'm think that I'll need some VBA code that will compare the registrant's tournament age to the ranges assigned to that tournament and will assign them to the correct age groups. Then there will be code to compare the registrant's experience and assign them to the correct division class. The key here is that each year might have a different number of age groups and experience groups as well as different experience group names. For instance in 2013 we may change the 8& Under group to 7-8 and add a 6 & Under group and we may add another level called Emperor for those with 20 years or more experience.
how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR JohnSmith 1988 .789 78 JohnSmith 1989 .854 85 JohnSmith 1990 .456 85
I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)
is there a way to count - in one query - the number of records that fall between specific times:
Time CountOfRecords 0000-0100 7 0100-0200 2 etc 2300-2400 4or do i have to do separate grouping queries then separate counting queries for every hour of the day? a crosstab? something else perhaps? feels like i'm missing something obvious (i hope...).
I have a table which holds parts sales data for 6 individual dealers, and I basically want to be able to produce a report which details the top 10 selling products by volume by group. I can produce a query which gives me the top 10 results of ALL of my groups accumulated, but really need this to be split by group, and would prefer not to create 6 different queries to obtain the data.
My table (tbl_Part Sales by Dealer) has the following fields:
Dealer Code Part Description Volume Sales
So I'd like the results to show the top Part sales by Volume, for each of the (6) Dealer Codes within the table.
i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..
