Hi, I have a main form with a subform inside it. The subform contains two records but only one is shown during form view, is there any way to show both records at the same time?
I am using continuous forms view for my subforms as i have to.
i have a table of time and dates and a value after it inn which i want to do a query which groups these values by every 3 hours e.g
my table
12/02/2001 00:00 6
12/02/2001 01:00 4
12/02/2001 02:00 8
12/02/2001 03:00 12
12/02/2001 04:00 7
12/02/2001 05:00 6
i want the query to do this
12/02/2001 02:00 18
12/02/2001 05:00 25
i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged
cheers
I've searched the forum and can't quite find what I need so I'm asking for further guidance form all you Access masters out there!
I have a field 'Time' in the format of 'dd/mm/yyyy hh:mm' which has a default value of =DMax("Time ","[Monitoring]")+1.
This increments 1 on the 'dd' bit when each new record is created, however what I want is for the hour (hh) bit to increment 1 on each new record.
Not sure if I'm being niave but I thought it was some sort of formatting issue, so I've dabbled with a few but to little effect unfortunately.
Hello Everybody...
I have a Question.
How do I convert a number (26) to 26:00 Hours.
The thing is.
I have a database that calculates the hours of overtime.
for example.
I work from 7:00 to 19:00.
Then I get 12:00 hours of total time.
The above is no problem.
But no I want to deduct the number 2 from 12:00 hours.
I hope to hear from u guys soon
Hi,
ive searched the forums for this but cant find anywhere.
how can i add one hour onto a time stamp please.
many thanks:confused:
Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?
For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.
Any help would be appreciated.
Paul.
Hello!!!
I've a trouble:
I have an Access DataBase with two tables:
1--Workers with fields (ID, Name and $/hour)
2--Marks with fields(ID, WorkerID, Datetime of mark)
Well, I need to generate a report with the following structure:
Name Worker / Number of days worked / Total Cost in $
---------------------------------------------------------------------------
Worker 1 / 19 / 13000$
Worker 2 / 20 / 21354$
Worker 3 / 12 / 12000$
...
Example of Workers Table Data:
ID / Name / $/hour
-------------------------------------------------------------------------
21367 / Worker 1 / 21,34$
54883 / Worker 2 / 54,87$
87364 / Worker 3 / 5,38$
Example of Marks Table Data:
ID / IDWorker / DateTime-Mark
-------------------------------------------------------------------------
6754-54 / 21267 / 19/05/2006 8:05:32
4327-12 / 54883 / 19/05/2006 8:07:12
7645-98 / 87364 / 19/05/2006 8:13:54
5432-99 / 21267 / 19/05/2006 14:05:32
7584-23 / 54883 / 19/05/2006 14:07:12
9485-23 / 87364 / 19/05/2006 14:13:54
... / 21267 / 19/05/2006 16:05:32
... / 54883 / 19/05/2006 16:07:12
... / 87364 / 19/05/2006 16:13:54
... / 21267 / 19/05/2006 20:05:32
... / 54883 / 19/05/2006 20:07:12
... / 87364 / 19/05/2006 20:13:54
... / 21267 / 20/05/2006 8:05:32
... / 54883 / 20/05/2006 8:07:12
... / 87364 / 20/05/2006 8:13:54
... / 21267 / 20/05/2006 14:05:32
... / 54883 / 20/05/2006 14:07:12
... / 87364 / 20/05/2006 14:13:54
... / 21267 / 20/05/2006 16:05:32
... / 54883 / 20/05/2006 16:07:12
... / 87364 / 20/05/2006 16:13:54
... / 21267 / 20/05/2006 20:05:32
... / 54883 / 20/05/2006 20:07:12
... / 87364 / 20/05/2006 20:13:54
The condition to increment number of days is:
YEAR(date-mark) != YEAR(date-mark) OR
MONTH(date-mark) != MONTH(date-mark) OR
DAY(date-mark) != DAY(date-mark)
for example:
1)
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
-----------------------------------> 1 days worked
2)
20/05/1982...time-mark // Worker 1
20/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
-----------------------------------> 2 days worked
Do you understand it?
Well, I've tried several SQL sentences, but I can't make the definitive sentence. Can you help me, please?
Note --> each Mark-Datetime represents an instant of time!!!
Okay,
Missing something simple here and can't figure it out. I want to display a time in military 24-hour format but can't get it to show the first "0" between the times 0000 - 0959.
Current statement is:
datTripTime = Format(datArr, "HH:mm")
What am I missing please???
Thanks
I have a very large dataset with over 11 million data records in three different databases! Currently the data is collected on average every minute, which is much too detailed! I would like to pull data for every site on the hour, or somewhere near the hour as some data was collected at different time intervals. (this latter part is a completely different issue, I would be happy just to get my first query working, then deal with data not collected exactly on the hour)
I wrote a query and tested it out on a very small portion of the data. It seems to be working, but for some reason won't pull data for the 2 pm, 5 pm, or 8 pm hours...even though the data is there in the table. Any reason why this may be? Even if I simplify the query to only pull out those hours, and not all 24 at once, the data still doesn't get selected. I really can't think of why it's not working. Any suggestions?
I've currently set up the Criteria in the time column of the query to look like this:
#1:00:00 AM# Or #2:00:00 AM# Or #3:00:00 AM# Or #4:00:00 AM# Or #5:00:00 AM# Or #6:00:00 AM# Or #7:00:00 AM# Or #8:00:00 AM# Or #9:00:00 AM# Or #10:00:00 AM# Or #11:00:00 AM# Or #12:00:00 PM# Or #1:00:00 PM# Or #2:00:00 PM# Or #3:00:00 PM# Or #4:00:00 PM# Or #5:00:00 PM# Or #6:00:00 PM# Or #7:00:00 PM# Or #8:00:00 PM# Or #9:00:00 PM# Or #10:00:00 PM# Or #11:00:00 PM# Or #12:00:00 AM#
Is there a better way to go about this?
Thanks!
Ok, I am still interested in knowing why the above query wasn't working should anyone know. But while thinking of how to deal with my other data not on the hour...seemed to have made a few queries that do the trick! Access is great...but sometimes very slow to get what you want done when you're still learning!
MY QUESTION:
I’m using Access 2002.
I’m trying to create a query that will COUNT the number of values within 1 hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that I need to have every hour bin represented whether there is any value in it or not. I believe that I need to create a separate table of the bins that I want (include bin start-value & stop-values) and then bring this table into my query and join it to the original table.
I was able to construct the make table query below to COUNT the values in hour bins for which there were records, but I also need rows for all of the zero values;
SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS [TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins]
FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter
GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI Detections.Date]), [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time])
HAVING ((([All NWHI Detections].Species)="tiger"))
ORDER BY [All NWHI Detections].Transmitter;
Q1? Creating the hour bins table
This table will be very large because it will need to have 4 years worth of hour bins for each transmitter (N=15) and location (N=12). This results in 6,307,200 hour bins! How can I write a query to create this table, or is there a better way of doing this?
Q2? Bringing the hour bins table into the main query.
How do I include the hour bins table in the main query to get my final result.
Any help would be greatly appreciated! Thank you.
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...).
Hi
I have made a create-tabel query that I want to run every 2. hour. How can I set Access up to do this automatically?
eroness
I forum could someone tell me:
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
regards in advance
Peter:eek:
I need to know about "Need to query result from access2007 find only "one maximum number" in hour per day every cell ID"
How I use sql command?
See my file at ...
I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.
View 3 Replies View RelatedI have a form in continuous forms mode...
Each entry is something the user is supposed to check on once and hour and update.
I have a "Last updated" field.
Now() goes into that field every time the record is updated.
I have a querry that will show the users records that are over an hour old.
How can I get Access to pop-up a window or something to alert the user they need to look at something? It would be really nice if Aspect would start blinking in the task bar or something.
Thanks!
What is wrong with this query?
=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))
What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.
I am trying to display on a form the average number of lines/records per hour that a person is adding to the database.
Trying to work with Dfirst and DLast I'd subtract the time of the most recent record from the time of the first record entered and then divide the number of records to display the average per hour in a text box.
I have a form with 5 subforms in it.
I am using recordsets to step through a table that contains a field with the names of the subforms.
Depending on the code, I want to make visible or hide the subform for that particular record.
ie Table has 5 records with a field called subForm. the values are as follows:
Record1: Field1: Value="Yes" Field2: Value="subForm1"
Record2: Field1: Value="Yes" Field2: Value="subForm2"
Record3: Field1: Value="Yes" Field2: Value="subForm3"
Record4: Field1: Value="No" Field2: Value="subForm4"
Record5: Field1: Value="Yes" Field2: Value="subForm5"
When I step through the table if the code finds "No" in Field1 then id makes the subform referred to in Field2 invisible and if it finds a "Yes" it makes it visible.
Can anyone help me with this?
Does anyone know how to have a datasheet view in a subform fill the top of the main form?
I want to be able to see the records in a datasheet view and when the record in the datasheet view is selected it prefills the top of a form ?
This is a question from yesterday that a member had helped me out with.
I have additional questions while I am waiting for response from him. If anyone of you can help, I would really appreciate it.
1. How can I make a form that gathers information from multiple tables?
2. Lets say it is possible, how does this info get saved to these multiple tables when the user clicks on the next record button on the record navigator.
Thanks for the assistance!
is it possible to have more then 2 subforms?
Lynda
Heya --
So if I haven't scared everyone away with the post's title, maybe someone out there can help me. I need it.
I have a many-to-many relationship with the following tables:
Companies
CompaniesProjects
Projects
I also have a query with the following:
CompaniesProjects.CompanyId
CompaniesProjects.ProjectId
Projects.*
Pretty straightforward. Now, I have a form to view Companies, inside of which is a subform listing Projects associated with the Company using the above query.
The issue that is stumping is that I would love to be able to add a new project from the subform. I thought it would be pretty easy, but I'm totally dry. I guess I need to grab the CompanyId from the parent form, and assign it to the new record before I can add any more data to the new record. But I have no idea how to do this, or even if it is the right thing to do.
Any help would be _greatly_ appreciated. I've been slamming my head against a wall with this for two weeks.
-Lucas
Hi,
I have 3 tables : Products, Description and ProductDescription. Products can have more then 1 description, Descriptions can be used by more then 1 product, that is why the third tabel ProductDescription exists (normalizing..:) ). The Productdescription table has 3 fields: id, productid, descriptionid.
I want a form in which you choose a product, then you see all the descriptions along with it. In this same form I want to be able to add a description, e.g. by pushing a button NEW. After that user enters description form etc. After returning to the mainform, a new record must be automatically added to the ProductDescription table and ofcourse a new record has been added to the Description table. I thought I would use subforms to realize this but it isn't working..:( . I have tried so many things that I dont know anymore what to do, i am totally confused. I know how to program in VB and ACCESS, but for now i don't see it anymore. Can someone please help me with this?
I just need to know how to start, what to use as subform and what as masterform, which links between fields, etc
nelleh:rolleyes:
Just a quicky.
I am trying to design a form with three subforms.
I keep getting tblXXX not in recordset.
Is there perhaps something wrong with my relationships?
Thanks,
Brian.
Zimbabwe.
Hello,
I hope someone could come up with a solution. I have a form and 2 subforms. The form contains the person id, address and phone number. The subform1 contains a list of skills that person has which is derived from a query. I have to be able to delete any of the skills that person has. I am not sure how to delete the skills but I know when I type a skill, it automatically adds (can grow = yes). In subform2, the skills that the person DOES NOT have are listed derived from another query again. We should be able to click on any of those skills so that it will be an addition in subform1. I do not know how to do this.
Thank you for anyone who comes up with the solutions.