I have atable the is storing ID, attendance_year and meeting code. Iw ould like to query the table and identify the IDs that have 10 consecutive attendance years no matter when it may have occured. So IDs have more than 10 row of data but not necassrily 10 years in a row.
How can I do this?
table looks like
123|2003|WIN03
123|2001|SUM02
456|2001|WIN01
456|2000|WIN00
456|2002|SPR02
456|2006|SUM06
I have a table that lists computers I want to know the age of them for replacement purposes. I am using this function to populate a textbox on a form that updates my table. the results I am getting is a negative whole number I would like to get a positive number with one decimal place. so if computer was shipped September 24, 2010 my textbox would say 3.5 not -3
Here is my function
Code:
Private Sub Form_Load()
Dim theDate As Date
Dim age As Integer
theDate = Nz(Me.compDate.value, 0)
If theDate > 0 Then
age = DateDiff("yyyy", Now(), theDate)
Me.compAge = age
End If
End Sub
Firstly I have placed this in the general section as I am not quite sure if any vba would need to be created for the following. I would also like to report on the information but the answer may not be done at report level.
Anyway, I have created a very basic database that holds staff attendance and their reason for being absent.
tblattendance:
AttendanceID
AttendanceType
StaffID
AttendanceDate
What i would like to do is group consecutive dates if they are all the same type and show them as DateFrom and DateTo
So for example, for someone who is on holiday between 01/Jan/06 and 06/Jan/06 I could show that as:
DateFrom 01/Jan/06
DateTo 06/Jan/06
I am not sure if that is explained very well but I will try to explain a bit more clearly if needed?
Thanks for any help.
Hi there
I am trying to get a very simple thing done in Access and I must have spent almost over 2 days of Internet time to do so. My problem is that I need to get the difference in 2 records to show in a query.
e.g. MyTable has 2 fields: 1. MyDate 2. MtrReading
Everyday I enter date in MyDate and a number in MtrReading. And I need to find out the difference between today's MtrReading and Yesterday's Mtr Reading.
Any help is greatly appreciated!
Thanks
Hi,
Is it possible to count the number of consecutive days in a table?
I need a query to calculate for a particular customer the number of consecutive days thay have logged a record if its greater than 1
EG: Table layout:
CUST A RC1 05/05/05
CUST A RC2 06/05/05
CUST A RC3 07/05/05
CUST A RC4 08/05/05
CUST A RC5 15/05/05
CUST A RC6 18/05/05
Possible query results:
CUST A 4 Days First 05/05/05 Last 08/05/05
Is it possible to do something like this??
Thanks in Advance
I am having a problem, probably due to my inexperience with Access. Here is the seniero:
-I have a form/table set up for operators to enter numbers on a daily basis.
-I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values
--I have no idea on how to subtract a previous day's value from today's value and report that value.
- Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?
Example
Record #1
Date - 1/17/06
Value - 15,232
Record #2
Date - 1/16/05
Value - 14,111
Desired output
Date 1/17/06
Value - 1121
Hello,
I am trying to generate some query result on a form.
My DB consists of the following fields : tradeID and profit/loss.
How do i make a query to find the Maximum consecutive number of wins and losses in the database and display the results on a form?
Would really appreciate it if any of you have tried to sort and display results for finding consecutive number of events in any database!
Thanks a lot
I have a Student Attendance table and it would be great if I could get some way of automatically determining if the attendance status is Absent - Unexcused for two or more consecutive sessions in order for someone to get in contact with the student.
View 14 Replies View RelatedI have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.
If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.
If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.
I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.
Ex. The red column is what I am trying to create
Account Date_To Counter
12345 06/30/2015 1
12345 05/31/2015 2
12345 04/30/2015 3
19999 06/30/2015 1
Hi there,
Some scene setting to my problem. I have a corporate system numbering invoices consecutively globally. For a subsidiary in Poland this presents a problem - all invoices for the subsidiary must be consecutive. ie. where the globally issued invoices for Poland might be 1,4,5,7 these need to be renumbered before issue in Poland.
It gets more complicated as the Polish Office standard is to invoice in the format "PL/Year/Month/Number" with the Number restarting from 1 for each month.
My database is very simple.
Table 1 = Link table to Oracle corporate data
Table 2 = Local table of invoices already produced (Global invoice number and date recorded)
I have a filter to find all new invoices (unmatched items), a report to print them and an Append query to populate the new items to the Local table.
My challenge: (1) to recreate the Polish numbering scheme and (2) have it consistently when existing data goes up to mid month and a new append operation has to follow the invoice number sequence.
Any ideas? I guess Autonumber is no good and I have little array experience.
Thanks
I want to place a control in a report footer that will return the highest number of consecutive weeks that a profit was earned. The database has fields named WeekNo and Net. The Net field contains positive values (profit) and negative values (losses). The WeekNo field is an integer from 1 to 52 designating the week number.
For example if the figure in the Net field is a positive number I want the control to count the number of consecutive weeks that a profit was earned. I know how to get the value of total weeks that a profit was earned; this is not what I am looking for. The control must return the highest number of CONSECUTIVE weeks that a profit was earned over the year.
Any help would be appreciated.
102,34112,021502503130010010000,QAR,1,24,1,{NULL:0 },{5:Y},6,14.5,31-MAR-2011
Above data is in one field , i want and expression to extract the data between two consecutive comma set by specifying the comma range.
For example if i specify range 2 to 3 then formula should return "021502503130010010000"
Is it possible to list out each consecutive date in a report when given a range of dates without having to create a table of dates?
For example, given the date range 12/01/2013 to 12/05/2013:
In report:
12/01/2013
12/02/2013
12/03/2013
12/04/2013
12/05/2013
Is it possible to do this dynamically in vba?
I want to ultimately join this to a recordset for the report.
I am setting up VBA to run multiple append queries in consecutive order. The append queries are supposed to pull a four digit number off of a form that will allow them to know which data to append. I want the number to be stored as text to correspond with the tables I am working with, but I am having trouble getting the form to work.
Here is how I would like it to work:
I enter my 4 digits in the text box on the form. Say "1305" for May 2013. I want to then run my vba (my queries update using the forms!txtupdatequery!textbox I have put in the criteria of the queries) and all of them run.
I enter my date (as a number but I want it to be text) and the form gives me a #name? error.
I am trying to create a form that creates an automatic identifier with a value one greater than the previous identifier. I was able to create something that updates in batches of two: That is, I end up with identifiers that go 1, 2, 2, 3, 3, 4, 4, etc. How do I get it to create consecutive identifiers (1, 2, 3, 4, etc.)?
View 2 Replies View RelatedI have a quick (hopefully) query about adding up consecutive values to get a running total.
Currently I have the following
ID Value
1 0
2 5
3 2
4 0
5 1
6 30
7 2
etc...
I am looking for a way to get a running total up to the point of a 0 then restarting. e.g.
ID Value Running total
1 0 0
2 5 5
3 2 7
4 0 0
5 1 1
6 30 31
7 2 33
I have list of candidates shown in report. I need each candidate to have a unique number based on user input. And this number increases by 1 for the rest of the candidates in the report.
Example is below:
If user enters 1132 in the user input box then the numbers shown in report should be as below:
Name Assignment Number
Felicity Thomas 1132
Andrew Sen 1133
Andy Luker 1134
Similarly if number entered is 2345 then the numbers shown in the report will be:
Name Assignment Number
Felicity Thomas 2345
Andrew Sen 2346
Andy Luker 2347
Hello mates, :cool:
I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P
Firstly, when designing tables, how can I format Date/Time to just the Year? I've tried syntax yyyy, but when I enter a year after opening the table, I get a type mismatch error.
Secondly, providing I have the correct custom format for Year, how can I enter the following dates?
1744-1745
Circa 1925
Before 1690
1800s
And would I be able to run queries on them, showing the dates in ascending order?
Any help would be much appreciated!
Hi,
I would like to be able to run a query that lists the number of years members have been subscribing (up to the current year).
i.e. to show the year he joined, and how many years he has been a member (shown as a number).
Is there a way to do this easily?
thanks
Adrian
Happy New Years guys, all the best. hope the year will be good to us all.
View 1 Replies View RelatedHow do I set up a query to sum data separately by year in the same row of data? I would like to see on one row a sum for year 2005 and also a separate field to sum for 2006.
Help me please!
EWJRichardson
I’m after some database advice. It will probably be something simple I can’t see for the wool…
I’m putting together a database that describes specifis small area geographies (Scottish Census output areas). For small area there will be various training category variables with associated counts and a general cost for all training in that geography. On its own this isn’t really a trouble but the database will grow over time with the same data (more or less) appearing each year to add to the database. I have a master geographical index that is used to aggregate small area data to higher areas so I could :-
- have a series of year based tables linked through the small area field and queried on a year look up basis.
- store each years data into the same table as the last and use a year identified to separate the records
- add new fields for all variables for each year across one table although once the number of fields reach 255 or so it would fall to bits.
I think the third is the worst option but I really would be grateful if anyone had any thoughts you could share with me on the best design approach to take.
Any help gratefully received!
Thanks...
Need some help figuring out the best way to handle this.
I have one table - tblEngineVolumes which has columns for each month Jan-Dec for the year 2005 with fields for each printer type.
Type Jan Feb Mar.....
PrinterA 100 50 300
PrinterB 500 25 250
I now have to figure out a way to have both 2005 and 2006 volumes for each printer.
My option so far is are below (there will never be any more than 2 years involved)
Type Jan 05 Jan 06 Feb 05 Feb 06
PrinterA 100 150 50 60
PrinterB 500 400 25 35
OR
Type Year Jan Feb
PrinterA 2005 100 50
PrinterA 2006 150 60
PrinterB 2005 500 25
PrinterB 2006 400 35
Would appreciate any thoughts/suggestions
Thanks,
Toni
Hi,
I have the following function:
Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
I call on the function in my query to determine the FiscalYear for a date.
Expr1: GetFiscalYear([ProdDate])
It sends back the correct Fiscal Year. However, when I try to put criteria in for Expr1 to show only Fiscal Year 2007 instead of all fiscal years, it gives me a data mismatch type error.
The [ProdDate] has the following formula:
Prod_Date: IIf([40Day]="True",DateAdd("d",-40,[Date Code]),DateAdd("d",-50,[Date Code])) where [40Day] returns True or False to determine if I should subtract 40 or 50 days from the Date Code. The Date Code is in the format ##/##/#### and is sometimes null.
I have tried everything I could think of and am completely stumped. Thanks in advance for any help!!!