How do I get just 41 records, each site, plus just the most recent event for that site?
Table 1, Sites (41 Records)
Table 2, Event (42 Records, 1 event for 40 sites, plus 2 events for 1 of the sites)
Totals Query 1, Group on SiteID from Sites, Select Max Date from Event. Good Result. (I get 41 records, 1 for each site, and the site with 2 events shows only the later event)
Query 2: Pull in Query 1, multiple fields from either the Sites Table or the Event Table. Good Result (41 records)
Query 3: Pull in Query 1 and both Tables. Everytime I get 1641 records. I've tried every possible join combination, changing query 1 from Dynaset to Snapshot, etc.
In a help screen I noticed that the sample shows a join with an infinity sign at one end, but I never get that graphic with my joins. Is that an old graphic, or am I missing something?
hi guys,I want to group a field in my query within a certain period of time, say 1 week. (e.g. between #12/5/2005# and #12/11/2005 23:00:00#) however I need to run this query every week.so I don't want to have to change the dates every week just to run the query.Is there a way to use the system's calendar date so that the values will be input automatically (i.e. to group between today and today-6 23:00:00)?? maybe using date() function or something??:confused: heaps n heaps of thx!!:D
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 have a table called tblTransactions with columns called (among others) [Date Time], Ticker, Quantity, Price and Commission. Column [Date Time] contains both a date and a time. I want to determine the commission paid per day. If I use the following code I still get all single trades on a day as the times at which the trades were exucuted are all different. Is it possible to group by full days only without having to change my database in order to determine the daily commission paid?
SELECT T.[Date Time], sum(Commission) AS TotalCommission tblTransactions AS T WHERE T.[Date Time]<=#04/17/07# GROUP BY T.[Date Time];
I have used Allen Browne's suggested method of ranking to rank by the date each client settled... Now I need to group the rankings by the referring company as below... so it ranks each client referred by referral company from earliest to latest - like so:
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator FROM MatterContactsMade GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator])) ORDER BY MatterContactsMade.MatterId;
I have a date field in a form fed by an option group. Most of the options return the correct values, but I'm having a hard time with this:
If the inspection date (in another form) is in Jan Feb or March, then return the date 1 Apr of the inspection year. If not, then return 1 Apr of the following year.
I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?
I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?
--- OR ---
Can it notify the user that they need to change the Option Group?
Below is a simplified example of what I'm trying to achieve with a parameter query.
The source table for the query contains two fields:
Reading_Date (short date) and Use_value (integer)
The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:
SELECT Sum(Table1.Use_Value) AS SumOfUse_Value FROM Table1 WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));
This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.
Table: Transactions: ID,Date,Title,Category,Value, Budget Name Table: Budget: Budget Name Budget Years: Budget Name, Date From, Date To
Form entitles Budget, Fields : Date From, Date To, Budget Name
So within the form budget I have a subform which I want to group the categories and get a total value. THE tricky part is, I only want it to include values from the budget specified in the form and the date range of the form.
I am collecting data every hour on the hour and I would like to group this data according to Shift (A, B and C) by Date. The only problem I am having is- how can I keep the overnight shift together as one date? 10pm - 6am
I was able to do a simple query where I was able to assign an A, B, or C depending on the field with the Short Time format using nested IIF statements. From there I was able to group by shift (A, B, or C) per date. However the C Shift is split up.
Example. Can Sunday October 5th 10pm to Monday October 6th 6am be grouped as one date.
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 want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?
p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???
I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...
I think maybe I should upload the database, let me know.
Here are the names:
Table name: PPM Data
Fields: Company PPM Month PPM Amount
There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.
I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.
Hi, i have a combo box that query a tabel to get the date's i chose from but she repeat the samedate if i have a 2 records with date 12-9-2005 i see 2 in the combo box ho i can join them to see only one date fron them
I have a field on a table that is a memo field. I am trying to write a query to include this field but get the "Can't group on memo or OLE object. Any ideas on why and how can I query with this memo field included?
I'm trying to view the last two valuations for each property so that they can be compared. I can pull out the last valuation for each property and can pull out the top two records but not for each property, just shows me the top 2 of all records.
