Tables :: How To Store Records

Mar 20, 2013

I have a table with with records that need to be captured for current and historical reporting. This is my table:

[Incident] [planned_end_date] [Report_Date}
1234 3/8/13 2/28/13
1234 3/8/13 3/1/13
1234 3/10/13 3/2/13

Then, on the next report dated 3/3/13 the incident may not be on the report because it is closed.

I need to be able to report on a count of incidents that meet different criteria at any point in time. I first made [Incident] and [planned_end_date] the PK but then when the [planned_end_date] changed it wouldn't be imported. If I have no PK all records will be imported and I would have the data I need but I think the table will grow too quickly (40 new records per day).

View Replies


ADVERTISEMENT

Multiple Selects And Records Store

Dec 21, 2005

I'm creating an Access data entry form for computer installations.

Basically the entry form will choose a software and license, then select one or more computers to install the software on.

Then later we can create a report that can track what softwares are installed on the computers, or track what computers has what softwares.

Now my question is for each record of the installation entry form, after we select the software, then we will choose a computer name or more computer names.
If just one computer is easy, but how can I choose multiple computers, which control should I use? and how can I store the computer name list in the installation table, do I have to make each computer installation a record in the table or put multiple computer names in one record in the field like memo, but if I use memo, later it seems i cannot group or search memo field?

Can anyone give me some ideas about what is an easy design solution or samples about these?

Thanks a lot

View 1 Replies View Related

Modules & VBA :: Store All Records In One Field?

Feb 20, 2014

I have a table like this :

Fields 1 --- field 2

A --- tuesday
A --- wednesday
A --- thursday
B --- tuesday
B --- wednesday

I want to end like this :

Fields 1 --- field 2

A --- tuesday,wednesday,thursday
B --- tuesday, wednesday

I was thinking of doing a loop in vba with recordset and a filter on field1 and concatenate the days of field2 in the first reccord of each letter.But my table is big so my code needs to be fast.

how to do this ?

View 3 Replies View Related

Tables :: Most Efficient Way To Store Historical Data

Mar 11, 2013

I'm thinking of 2 different ways, but not sure how Access will handle them.

1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).

This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).

2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)

I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?

View 14 Replies View Related

Tables :: Calculate And Store Time From Dates?

Nov 11, 2012

We are collecting data from client files that have multiple dates (start_date, event1_date, event2_date, end_date.) For client protection purposes, we are absolutely not allowed to leave the facility with the actual dates. However, we want to know how many days have passed between each event. I thought about having the data collectors enter each date, have a calculated field store the time between dates, and then have the actual dates cleared when the record is saved or when the person moves to the next record.

View 14 Replies View Related

Tables :: Check Box Field - Store Only Two Possible Data Choices

Oct 7, 2014

Is it possible to create a field in a table that stores only two possible data choices: a check or null value ?

View 6 Replies View Related

Creating A Database To Store Training Records That Saves A Scanned Copy

Jun 8, 2015

I'm a access novice who is looking to create a database to store training records for permanent and agency staff, and contractors staff. I need the database to be able to produce reports on how many courses were trained over each month, and the total duration of the course completed (in hours).

View 2 Replies View Related

Tables :: How To Get Information Inputted On Form To Store In Correct Table

Mar 4, 2014

I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.

Here is what I am wanting to do:

I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate

I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.

View 14 Replies View Related

Modules & VBA :: Write A Procedure To Send A Separate Email To Each Store That Contains Records Found In Table 2

Feb 9, 2015

I have two tables

1) has email address, and Store ID
2) has multiple records per store

I need to write a procedure to send a separate email to each store that contains the records found in table 2 ( excel format).

View 3 Replies View Related

Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?

Nov 21, 2012

I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).

I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.

View 3 Replies View Related

Tables :: How To Combine Records Of Three Tables Into One Table

Jul 30, 2013

I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.

View 3 Replies View Related

Tables :: Deleting Matching Records From Two Tables

Dec 5, 2012

I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.

View 3 Replies View Related

Tables :: Create Tables From Records In Field?

Sep 5, 2013

I'm trying to create a table for every record I have in a field. I have two tables, one titled "Experiments" and another titled "Students". I want the new tables generated from the field EXP_NAME from Experiments. I need it to have seven fields, one copied from Students, five labeled Trial1 thru Trial5, and a final for a grade.

View 10 Replies View Related

Tables :: Deleted Records In One-to-one Tables

Apr 25, 2014

My db includes two tables, Employees and Users, both with a pk of EmpNum (Autonumber in Employees; Number in Users). Users is a subset of Employees, and in the Users table, the row source for EmpNum is a query of the Employees table. I had a relationship defined with a one-to-one between the two tables, but did not have Enforce Referential Integrity or Cascade selected.

The problem is, I deleted a record out of Users, but it also automatically deleted the related Employees record. Re-creating the employee was quite the task because all related records in several other tables also disappeared. I experimented by deleting the relationship between the two tables and then deleting another user, but it still deleted the related employee. How can this be? The two tables are no longer linked, other than the Users EmpNum lookup.

View 6 Replies View Related

Tables :: Moving Records Between Tables?

May 7, 2014

I have been tasked with creating a simple database for work.

Basically the table tracks the progress of various employment checks. So example headings are Name, CRB, Occupational Health etc. Now basically, once all the checks are completed, I need the record to be moved to another table named 'Live'.

Is there any easy way to achieve this, or will i have to manually move each record once the checks have been completed.

View 3 Replies View Related

Copying A Set Of Records Across Several Tables To Same Tables With A Few Changes

Apr 8, 2015

There is one main policy table, and a collection of related tables that store information associated with each of the policies (coverage amounts, agents, etc). What I'm struggling with though is where they renew every so often (most times it's each year, but some go three or more years). Because I need the database to show everything EXACTLY as it was on each policy term, even expired ones, I don't want to just edit the existing records when policies renew; I need to create a copy of the ENTIRE record (across all related tables), but with a new PK, and the ability for users to change a few items, such as the policy number and effective dates.

There are two keys that I use to identify policies: one is the PK, which is an auto# that will need to change, and the other is a small text ID set when the policy is first issued, in order to make sure that even when policy numbers change from year to year, we can always track the history, so of course that one will need to remain the same. If I have the user click a "Renew" button on the policy view form that goes to a small form that takes what changes I want to allow and converts those into variables, could I then use those variables in a query, and have it copy all of these related records, with the other tables taking the new PK from the main table to keep the overall policy record linked?

View 4 Replies View Related

Store Month Only

Jan 9, 2006

if you are asked to store only the month, or only the month and year, how do you do it? tnx.

View 3 Replies View Related

Store A Calculated Value?

Jul 9, 2007

I was once told that Access does not like to store calculated values but I would like to.

Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100

This gives me the value that I desire, now I need to store it in the table with the rest of the records data.
I am planing on storing 3 -4 expressions like this and then averaging them.
Then building a query to the averages for the day,week,month, and year.

Please be gentle I am not very familiar with access but learning a lot:)

View 7 Replies View Related

Divide And Store

Mar 17, 2005

Hi hope someone can help,

What I’m trying to do is work out price per quantity, at the mo I have a database that has products and price so lets take paint for an example.

If I have a 5 litre pot of paint and I’m selling it for £10 I would like a field that tells me the price per litre, so what I’m think I need to do is have a field called ”price” and divide it by a second field called “quantity” and output the sum to a third field called “price per litre” which populates the database.

Sadly I haven’t got a clue how to do this with access and if anyone can help it would be much appreciated.

Thanks

View 1 Replies View Related

What Is The Best Way To Store A Picture??

Jan 12, 2005

In everyones opinion what is the best way to handle picture uploads in an Access database?

One picture for one employee, where should I store the pictures. I know that keeping pictures in the database eats up space and really can slow ya down. Make a new folder just for pictures??

Thanks fellas and ladies.

View 2 Replies View Related

Store Calculations

Jan 15, 2005

Ok I know everyone is going to say you should not store calculations, but I have no choice!!!!!!!!!! I have the form ORDERFORM (recordsource ORDERS table) with a subform ORDERFORMsubPROD (recordsource ORDERSPROD table). Essently theses 2 forms togather allow me to place an order for multiple products and store them in the ORDERS (key: ORDERID) and ORDERSPROD (foregin key: ORDERID) tables. The ORDERFORM includes information such as an ORDERDT and the ORDERFORMsubPROD includes a sum of cost the products ordered which is shown in ORDERFORM with a boundcontrol = to the ORDERFORMsubPROD control that does the actual calculations.

Next I need to be able to pull specific informtaion from the ORDERS table and group it togather by the SALESMANID. To this end I have a form SALESMAN with a subform SALESMANsubORDERS that displayes the ORDERID, ORDERDT, and the calculated sum of the products ordered on that ORDERID based on the SALESMANID of the order.

So far everything stated above is correct and should not require that I store calculations. My problem comes in when I am trying to make a form that will filter this information based on user supplied criteria (my user does not want to have to use the filer buttions supplied by Access). I'm using DoCmd.OpenForm and setting my WHERE criteria based on a bunch of If .. Then statements that check the various filter text/combo boxes to determine if they are populated and then admend the WHERE statement accordingly. At least that is how it works on a very similar form that filters ORDERFORM. I have discovered that because the subform, SALESMANsubORDERS, is based on a query that includes calculations the WHERE statement needs to be phrased as HAVING. I can not figure out how to get Access to comply with this so all I can think of is to store the calculated values (and be able to allow them to be updated later) then pull them directly from the ORDERS table.

Anyone who can write precise/generic code and most importantly tell me where (what event) that code should be placed on please let me know. This is the first time I have run into this problem and it is really my last problem before completing a DB application that needs to be completed by Wed 1/19/2005. Thanks in advance for any advise/help I receive.

View 1 Replies View Related

Store Pdf Drawings In Mdb

Oct 7, 2007

NE1 help me with this?
I want to store some pdf drawings in an mdb.
I want to be able to search just by the file name of these drawings.
I want to be able to click on any one of the file names I find and have adobe display the file.
I want to compile this into an mde to make it a stand alone app.

I can do this using asp on a web server. But I can't seem to figure out how to do it just using access without asp.

View 2 Replies View Related

Best Ways To Store Data

Apr 12, 2006

Hi
I am creating a contact management database.

I have set it up and now need to create an invoice section. An invoice needs to be issued every month and is worked out on a percentage of works complete. For example in one job there may be 10 items of work and for 8 of these iems 25% of the work is complete and the other 2 no work has started.
Therefore my invoice needs to be able to calculate 25% of the value of the 8 items and work out a total price to be invoiced. Each time an invoice is created I need to be able to store the invoice value and the percent value of each item complete.
Does any one have any ideas as to a good way of doing this. That is if you can undersatnd what I am asking for. Thanks for any help.

View 2 Replies View Related

Where To Store Misc. Data?

Jun 30, 2005

I have a handful of misc. data that is unique an really doesn't fit anywhere... I need it to be easily updateable, as it will and needs to change from time to time, What should I do with it?

Some of the data is a "Common Footer" that prints on ALL reports and DOES change periodically. I decided to create a common footer table with a single value and can access it from all of my reports... it works great!

However, I now have a bunch of other misc. data that I need to store, like the "Working Year" (I am going to use the field to dictate which year of info to pull the data from) "Common Header", etc. Additionally, I am considering storing formatting data such as font size (still considering this).

So with all this explained, to sum it all up, what is the best method for handling all of this misc. data? I didn't think creating a separate table for each piece was the best idea.

Thanks for your suggestions!

View 1 Replies View Related

Pictures : Store And Display

Dec 17, 2005

Lets say an employee form withs the name, the position, the birthdate, ... and a picture of the person.

There are several ways to store pictures in a database, each having pros and cons.

Experienced developpers in dealing with pictures would be kind to tell me the way they recommend to store the picture, and the best way to simply display it on the form.

Thanks,

View 7 Replies View Related

How To Store Selected Songs Ids In Db?

Feb 18, 2006

Hi all i have a db for my music collection. It has one table with the following feilds on it:

ID|filename|artist|album|title|track|comments|genr e|seconds|filesize|bitrate

I already created pages that with checkboxes that user select indvidual songs and by click of play button it goes and plays those songs for me.

Now i created another button next to play that it supposed to write the selected songs to db and save the ids of slected songs.Note: i use song ids to pull indvidual songs from db to be played by my song player!

My problem is i do not how to design a few tables that keep track of selected songs for each user along with the name of playlist for each user!! In processs i do not want to modify my current table i just want to add more tables to help me achive what i want. I be happy if an expert tell me how i can create tables that store play list(selected songs)info for each user and later i be able to query them easily in order to pull out playlist of each user.Thanks

View 3 Replies View Related







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