Tables :: Tracking Several Years Of Data?

Oct 26, 2012

I am looking to track the salary information for my small firm over a number of years. Does it make sense to create a table with multiple fields that would be structured like this (2011401(k), 2011Medical, 2011 LifeInsurance, 2012401(k), 2012Medical, 2012LifeInsurance, etc) or is there a better way to tackle this?

View Replies


ADVERTISEMENT

Tables :: Tracking Data Associated With Samples

May 10, 2013

I'm looking for some input on table design.

I work in a lab and am looking to track data associated with samples on PCR plates usually in 2 formats

96 well (8x12) rows A-H and Columns 1-12
384 well (16-24) row A-P and Columns 1-24

each well will have at least 3 pieces of information associated with it:Sample
Assay
Qulaity Score

but more may be added as I progress with the design.

field such as run name run date and operator for each plate would also be needed

What would be the best way to structure the table?

Obviously listing well A1, sample in A1, QA for A1 etc etc as fields wouldn't work as theres a 255 field limit and 3*96 = 288 and thats before we even get to 384

I could have a table for each run but that feels like bad design to me, as if you wanted to look at quality trends across time you would need to query multiple tables and query the date associated with each to even knwo which table to look in

A table for each well along with its QA score, assay and a look up field linking it to a list of plates, which would contain the run date etc. (this table listing runs is already present in my database)

Some background on current structure

Tables

Booking - contains a list of worksheet numbers along with info such as submitter, and conditions for the test such as assay

Experiment - expirment number conditions and associated DNAs

DNA - a list of dna samples

Plate - name of plate and info such as run date and associated worksheets

View 4 Replies View Related

Modules & VBA :: Getting Age In Years With One Decimal Place (3.5 Years)

Mar 24, 2014

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

View 4 Replies View Related

Same Data Many Years = Confused!

May 10, 2005

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...

View 1 Replies View Related

Multiple Years Data

Aug 22, 2005

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

View 2 Replies View Related

Need Better Tracking With My Tables

Oct 19, 2005

I have a DB thats working fine, but (there always is a but) I now need to do something a little bit different. I need to be able to track my originators and who they have brought into the company. So I will have an originator and many down-line originators, and these people that come in down-line will have people coming in down-line from them and so forth and so on. I have a table that has all originator info and originatorID, but will I need another table or can I just add to the exisisting one. Here is the table structure:

‎Originators:
-------------
OriginatorID -autoNumber
FirstName - Text
LastName- Text
OrigSSN- Text
Address- Text
City- Text
State- Text
PostalCode- Text
HomePhone- Text
WorkPhone- Text
CellPhone- Text
FaxPhone- Text
EmailName- Text
BirthDate- Date/time
Level- Text


If more info is needed please let me Know.. Thanks in advance.

Scott

View 4 Replies View Related

Tracking Down Linked Tables

Feb 1, 2006

I am trying to deconstruct a database that has several linked tables. Is there a way to find the source database for the linked table from within the original database?

View 3 Replies View Related

Queries :: Comparing Data From Current To Previous Years

Oct 9, 2013

I have a table which is used to record electric billing period dates, total kWh and costs for a list of locations each month. I would like to compare the quarter total kWh and costs for this year "2013" against same quarter last year "2012". I have created two queries, the 1st is capturing data from 2013 and the 2nd is capturing data from 2012. I created a 3rd query which joins the 1st and 2nd together by month where fields match. Both tables have the same fields, however there is more records in 2012 than 2013. How can I join the tables together so months that have no data are left blank?

View 14 Replies View Related

Tables :: Task Tracking For Making Item

Aug 24, 2013

Im trying to create a database to track who worked on what item and on what date, with four tasks required to complete the item.To give an example:I have a factory that builds Lego models, for each model there are four steps:

1)Unbox Parts
2)Sort Parts
3)Build
4)Check build

Any employee can work on any task, and multiple tasks can be done by the same employee.Having a completed item table with a field for each task, and a date for each task, with one to many relationships from employee id to each task type. This failed as I needed many to many, so I made a junction table to link them, and this is where I get a little muddled.

Do I need a table of task types, then a table tracking each task to feed into the item table, or is there a simple solution I'm overlooking?

View 4 Replies View Related

Queries :: Show Data Older Than Specified Number Of Years From Future Date

Apr 22, 2014

Querying a field for a number of days from today's date but not had a lot of luck finding how to query this from a date in the future.

Currently I have as criteria for the relevant field:

Code:
<[Period Start (dd/mm/yy - less 5 years from financial year end)]

which prompts for a parameter but using this I have to figure out the date 5 years from the future date first and then enter this.

Surely there must be code that allows me to simply enter the date, in the prompt and this then shows me data of 5 years or older equipment!?

View 8 Replies View Related

Tables :: Tracking Number Of Orders A User Makes

May 7, 2014

I want to track how many orders a user makes. So every time they place an order it increments 1 after each order.

I currently have the following tables,
Order
OrderDetail
User

Should i create an UserOrderHistory table?

Eventually i want this information to form part of an order number. eg

196(order primary key) AR (UsersInitials) /23 (number of orders User has placed)

View 5 Replies View Related

Tables :: Claim Tracking Database - Structure For Dates Of Events Related To Claims

Feb 28, 2013

I am developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.

I currently have the following tables set up:

tblClaim
ClaimID
ClaimNumber

tblEvents
EventID
EventName

tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate

With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?

I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.

View 4 Replies View Related

Tracking 250 Pieces Of Data Per Client. Table Structure Help Needed.

Mar 12, 2006

hi everybody,
great resource you have here! my employer is tracking around 250 pieces of data for each client at our facility. i am making a new access system based on their existing mysql database and web front end. before i start messing around with forms and reports, i want to see how well this existing structure will work in access, and what kind of approach i should take. i am a newbie with access, but lots of experience with asp/mssql/php/mysql. making web forms is so time consuming that i figured i would be best off moving the whole thing to access and starting from scratch.

client information is stored in eight tables. each table has around 30 fields in it. the first table has a primary key autonumber, and the other seven tables have foreign keys with unique constraints that point back to the first table. that is, for each client record in the first table, there can only be exactly one corresponding record in the other tables.

i did some data massaging, and got the eight client tables into one big table, but the resultant table has almost 250 fields in it, and access doesn't seem to like working with tables that big. so i am thinking that it is best to leave the eight tables separate, but linked in one to one relationships.

i was kind of ideally visualizing a form with eight tabs so that i could edit/update all of the information from the eight tables rather seamlessly.

my question is: what approach to table structure will best suit my needs, and what approach should i take to add/update/delete the info with forms? will i need to do vb for this? any good one-to-one example databases anybody could point me at?

thanks a million,
harry doyle

View 1 Replies View Related

Project Tracking Reports - Working With Monthly Data Updates

May 27, 2012

I receive monthly project tracking reports and I'm looking to compare a few things between these monthly updates. I should add I'm new to Access and have been using Excel and dual monitors to manually compare data sets!!! My life might be getting a LOT easier.

Here's what I'm tracking:

I get monthly excel reports that have unique values from a "Project ID"each "Project ID" stays the same every month, but the project status changes from "Installed", "Commitment", "Pre-Commitment", etcSometimes new projects are addedSometimes old projects disappear from the reportA few other columns have numerical data that changes every month as well "Project Cost", "$ Incentive", etc.

Here's what I'm trying to automate:

Find out what Project IDs are newFind out if old Project IDs are missing from the new reportSomehow "flag" or identify projects that have status changesSomehow "flag" or identify which numerical values increased or decreased i.e. "Project Cost", "$ Incentive", etc

Do I create two tables? Do I append the new monthly report to the old one when I import my data, then somehow work with the new information?

View 1 Replies View Related

Tables :: Attendance Tracking System - Adding Hours For Each Student Enrolled In Class Session

Jul 12, 2013

I'm creating an attendance tracking system, the attendance system needs to : Allow an instructor to enroll students into one or more classes, and then add attendance by date to each class(es). The problem that I'm having is, attendance is tracked by the number of hours that a student attends class. For instance, if class1 is in session from 8a.m. - 12p.m. student1 may only stay 2.50 hours of that class and student2 may stay 3 hours. The teacher needs to be able to add attendance to class1 for July 12, 2013 and add hours for each student enrolled in this class session who attended on that day. I have the following so far, minus the attendance:

Students -This is just the basic demographic information that instructors will need to enter on students.
StudentID (PK)
StudentLastName
StudentFirstName
DateOfEnrollment
LevelOfStudy
DateOfBirth

Location - These are the locations in which our students can attend classes
LocationID (PK)
LocationName
County
City
LocationPhoneNumber
LocationFaxNumber

Classes - These are the classes that our students can attend.
CRNNo (PK)
ClassName
LocationID
InstructorID

Instructor - These are the instructors that teach our classes.
InstructorID (PK)
InstructorLastName
InstructorFirstName
InstructorEmailAddress

Enrollment - This table enrolls the student into a class
StudentID(PK)
CRNNo (PK)
LocationID (PK)

View 11 Replies View Related

Add Months And Years

Oct 29, 2007

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

View 1 Replies View Related

Working With Years (please Help!)

Feb 14, 2007

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!

View 2 Replies View Related

Years Query

Oct 2, 2007

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

View 2 Replies View Related

10 Consecutive Years

Dec 5, 2007

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

View 5 Replies View Related

Happy New Years

Dec 31, 2004

Happy New Years guys, all the best. hope the year will be good to us all.

View 1 Replies View Related

Sum - Two Years In Different Fields

Oct 31, 2006

How 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

View 4 Replies View Related

Calculating Fiscal Years

Dec 4, 2007

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!!!

View 2 Replies View Related

DESPERATELY In Need Of Help On Query Last 2 Years

Mar 5, 2008

Hi,

How do I query the last 2 years vs. current date?

Basically, I want to see the record from today date back to 2 years.

Please assist...

Thanks,
Isabel

View 3 Replies View Related

Drop Down List Of Years

Oct 11, 2005

Hi folks,

I want to populate a drop down combo box with a list of 10 years (current year +/- five years). I can enter them as a Value list but I don't want to have to update the list every year. Neither do I want to allocate a table to maintain a list.

Any ideas as to how I can auto populate the combo with a 10 yr span (5 previous and 5 next) and default to current year?

Thanks,

Dave

View 9 Replies View Related

List All The Years In A Combo Box

May 20, 2006

I hope someone will be able to help....

I have minimum year and maximum year value (for example 2006 is minimum & 2009 is maximum. How could I get a combo box to display all the years within the range of the minimum and maximum year (in this case, it should give me an option of 2006, 2007, 2008, 2009).

FYI - This database is used to track projects and each project has a commencement date and an estimated completion date. the minimum year is derived from the commencement date and the maximum date is derived from the estimated completion date. I am building a search form whereby the year selected in the combo box should display all the projects commencing, continuing, and ending that year.

Pls help...

Thanks in advance.
jan :confused:

View 8 Replies View Related

Calculate Age In Years And Months

Feb 8, 2005

I need a formula or a function that will return a person's age in years and months. All the functions and formulas that I have seen to date and that I have used only calculate a person's age in years. Can anyone help?

View 2 Replies View Related







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