General :: Set Up Values In Table Based On Starting Year And Length Of Period

Jun 12, 2014

I have a table period with three columns

ID, Period_Start_Year, Period_End_Year

I could have many periods in a future. What i want is to allow users to insert just first start year and lenght of period and table will be automatically updated based on these two values.

So user will insert Period_Start_Year and Length of Period, for example 2010 and 2 and table should be>

ID Period_Start_Year Period_End_Year
1 2010 2011
2 2012 2013
3 2014 2015

While i am writing this i realised that user have to specify also ending year, i do not want to have infinity.. I am thinking about form and update query but can't get it...

View Replies


ADVERTISEMENT

General :: Displaying Results With Starting And Ending Year

Sep 24, 2013

I have created a 6 search boxes with names Author, Published, Language, Title, Keywords and University. If I enter "A" in Author search field then it should return all the 6 fields which contains "A" in Author field. It worked well.

My Requirement :

The column published is the year. In search box of Published if I search for 2000 then it's returning all the 6 fields which contains "2000" in Published field.

But most of the people doesn't know the exact year in which the book is published so they need some favour like, If they search for 2000-2010 in Published field then it should return all the fields which are between 2000-2010 in the published field.

For this do I need to create 2 extra text boxes and name them as Starting year and Ending Year? or else we can achieve this in single text box as xxxx-xxxx?

View 6 Replies View Related

General :: UPDATE Field In Table Based On DISTINCT Values

Mar 12, 2013

Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.

PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1

View 6 Replies View Related

Reports :: Date Fields (Month And Year) Of Reporting Period

Mar 5, 2013

I need to put a date field in that is the month and year of the reporting period...

for example

February 2013

and then another date field that is the 10th day of the month following the reporting period.

example: March 10, 2013

I have tried a number of different formats and can't get it right.

View 4 Replies View Related

Calcute Week Numbers Starting From 1 July Of Current Year

Feb 13, 2007

Hi everyone,

I want to calculate the week number of a date in a year starting from the 1 July (of the current year) - as this is the beginning of the Financial Year in Australia.

The starting day of the week is Wednesday.

I've been trying to think how I could use the Access DatePart Function in a query to calculate the week number from sales records.

I have had no trouble calculating the week number from the 1 Jan using the DatePart function. However, the function doesn't allow me to select a different date other than the first week of January.

So as a work around, I've thought of calculating the week number of 1 July in the current year, and just using the DateAdd function to add the week number to the week calculated by the DatePart Function.

Not quite working yet. Here's what I've come up with so far.


WeekNumberCount: DatePart("ww",[DateCalled],4)

I use the following to calculate the "my" starting week of the year:

StartingWeekInYear: DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)


Then I add the two together together:

MyWeekNumberCount: DatePart("ww",[DateCalled],4)+DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)


I know it needs some work to be useful for all years. Any suggestions?

View 3 Replies View Related

HELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values

Apr 25, 2006

I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.

For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006

THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.

What do you suggest? Many thanks.

Mehran

View 7 Replies View Related

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.

Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).

View 5 Replies View Related

Period Based On Week Number

Aug 23, 2005

Hi,

I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.

How would I obtain the start date and the end date from just the week number?

Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.

Any help would be much appreciated.

View 2 Replies View Related

General :: Keep Track Of All Permits Per Year Per User In Same Table

Dec 3, 2012

We have a db to register permits that count for one calendar year. This since 2012. For next year, permits must be renewed. How can we keep track of all permits per year per user in the same table ? We would like to produce annual statistics as well ? Easy solution would have been to copy the existing table for the year 2012 and paste as a new table for year 2013 but we don't know yet who will renew his permit ...

View 3 Replies View Related

General :: Determining The Values In One List Based On Another

Jun 27, 2012

I have a table with a few Listbox options. I want one of the columns' values to be determined by the value chosen in another field. How do I do this?

Example: In column A I choose value ABC from the dropdown. I want column B to now offer values in that list of DEF, GHI, JKL. If I chose XYZ in column A instead, I'd want the column B list to show MNO, PQR, STU. Etc.

View 6 Replies View Related

General :: Getting Values From Table2 Based On Value Selected In Table1

Oct 3, 2014

I have a table named Product and Quotation.

This is what i want to achieve

In Table Quotation when i select product name from dropdown menu i want all the related information for that product to be visible in Quotation like product id, productdescription & productcost

View 2 Replies View Related

Automatic Recurring Entry Based On Start Date And Nominal Interval Period

Feb 15, 2012

How to create a table that has a recurring entry, based on a start date and a nominal interval period of e.g. one calendar month?

Is it possible to have these entries applied automatically each time so that they appear, as required, when the table is opened?

For example, for the entry of regular payments due per month.

View 3 Replies View Related

Update Query Based On Length Of Value In Field

Jul 12, 2007

I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?

View 1 Replies View Related

General :: Button To Print A Report Based On Text Box Values On Form

Jul 11, 2015

Im using a button to print a report based on a text box values on the form.the code for which is below

Code:

Private Sub SaveBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.RunSQL "Update BookInTable SET BookedOut = True WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.OpenReport "Labels", acViewNormal
DoCmd.PrintOut , , , , 1
DoCmd.Close acReport, "Labels", acSaveNo
DoCmd.SetWarnings True
End Sub

The problem that I am getting is not only is the label printing but so is the form.

View 3 Replies View Related

Using Boolean 'and' To Update A Table Based On Values In A Link Table

Feb 24, 2008

The attached Access XP file demonstrates my problem. I've included a form to make testing easier.

Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.

A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.

I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.

In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.

I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.

View 5 Replies View Related

General :: Pull All Records In A Rolling 365 Day Period

Apr 4, 2014

What is the best way to pull all records in a rolling 365 day period. Like from 4/3/2013 to 4/4/2014?

View 2 Replies View Related

Forms :: Changing Format Of Field Based On String Length

Jun 11, 2014

I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.

I have tried it using code:

If Len(Me.FormFieldName)=11 Then
Me.FormFieldName.Format = "@@@-@@@-@@@@-@"
End If

Is it even possible and if so is my syntax correct or am I way off base?

View 8 Replies View Related

Modules & VBA :: Replace Values In One Table Based On 2nd Table

Jan 12, 2015

I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:

ID LABEL CODE
1 Ashaway ASH
2 Barrington BAR
3 Bristol BRI
4 Jesse Smith BUR
....

Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.

What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.

I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty.

View 10 Replies View Related

Adding New Records To Table Based On Values In Another Table?

Aug 19, 2015

I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:

1. tblPatients - PatientID (PK), LastName, FirstName, DOB,...
2. tblLocations - LocationID (PK), LocationLabel,....
3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc
4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate
5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)

All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.

Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.

View 3 Replies View Related

General :: Rate Calculation Over Period Of Time - MDB Format

Jan 29, 2014

Calculating the rates over a period of time. I think the attachment will give you a clear picture of what I am looking for.

To get the cost for a particular period I want the rates to be updated as per the given period. How I could get the calculation in the mdb format...

View 4 Replies View Related

General :: Form Starting At Wrong Position Of A Page

May 9, 2013

I am a newbie to ms access and forms and I have a small problem with a form. The solution has lots of different forms with different tabs for each section. This particular tab has a few sub forms in it. When I switch into the specific tab in question, the screen moves down ever so slightly and I always have to scroll up to see all the other tab headings.Is there a way to do something by code or a property setting I can use to make the page start from the top without users having to scroll up each time?

I hope I have explained myself clearly enough, if not I will be more than happy to provide more info.

View 1 Replies View Related

Adding Value In Table Based On Values In Two Other Fields

Dec 3, 2007

Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!

View 12 Replies View Related

Grabbing Values From A Table Based On Form Entry

Jan 5, 2006

In my database I have 4 tables for a small painting and decorating business.

These are:
tblCustomers, tblOrders, tblStock, tblPayments.

I have a form based on tblOrders to add new orders when they come in from customers.

In this form there are 3 important fields: "CustomerID" (Lookup to tblCustomers), "StockID" (Lookup to tblStock) and Quantity (Entered by user).

However, I want a calculated field that will be worked out automatically when the user is entering these details. The calculated field should go to tblStock and grab the "Cost" field.

However the main problem is specifying what price it will grab from the table. I want it to get the "Cost" where "StockID" = (the stock ID selected in the lookup). I then want to multiply the price by the quantity which the user has entered.

The control source should be worded something like:
= ([tblStock]![Cost] where [StockID] = StockID)*Quantity

but that isn't valid.

Could anyone point me to a piece of VBA or some way to get around my problem?
Many thanks for any help,
Stephen

View 2 Replies View Related

Queries :: Sum Table Values Based On Combo Box Selection?

May 20, 2014

I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.

I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.

In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.

What do I need to change to get it to sum correctly?

I have attached the database below so people can take a look.

View 1 Replies View Related

Forms :: Open Form Based On Table Values?

Sep 8, 2014

I have a table with the fields Thermometer_ID (primary key) and CalibrationType (combo box list with the options of InHouse or SendOut)

I want the user to click on a button and have an input box to input the thermometer_id. Then if the CalibrationType is InHouse open form Verification and if it's SendOut open form Thermometers.

View 2 Replies View Related

Forms :: How To Get Values From Another Table Based On Drop Down List

Aug 6, 2014

In my form's table (tblMain), I've got a lookup field (drop-down list) that lists the primary key field from a different table (tblDiff). tblDiff includes 3 more fields. In my form for tblMain, I want to include 3 more textboxes that get filled up with these 3 fields from tblDiff when the corresponding primary key is selected in the drop-down box.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved