Create A Tracking Table?
Nov 4, 2013
I have my database and my form all created and working. Reports and everything are in place. But we need to have a record of what changes have been made. Essentially what I want to make is a table that is essentially a log of all the changes that have occurred.
What I have is essentially a time clock database. So when someone enters our facility the guard will check them in and out. What I want is a list of all the ins and out for a day. So it will be a running list. Then I will create a query to return results for a report. But where I am having problems is creating a table that will house this data. So out of my main table I want a child table that will have certain data fromt he parent table. Mainly the time in and out along with the name of the person. There will be multiple repeat data entries in this table and I am ok with that.
View Replies
ADVERTISEMENT
Dec 11, 2013
I 'm trying to create a database for tracking time off from work and print a weekly roster. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date2, Date3, Date4, Date,5 Date6, Date7
I now have a form. This form has the 7, fields Date1, Date2.... Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,Date3 = [Date1]+2 and so on until I have all 7 fields showing dates from Sunday - Saturday.These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2.. fields to show their respective Work Week codes by writing very simple If Then statements . So here is where I AM STUCK, When I am showing this week view how do I get D1 - D7 to identify what date they are supposed to be in reference to Date1 - Date7 then compare themselves to the table Time Requests to see if they have a match and then set the value of D1, D2 . to the Time code in that table. D1 - D7 need to auto populate and do this for about 50 employees.
View 2 Replies
View Related
Jun 14, 2013
I'm doing a database that tracks customer projects and orders.
The Header table contains all the Customer and Project details like customer name, contact details, project leader, etc.
A second table BOM contains the customer orders for a particular project.
I need a way to track changes or revisions to the BOM. It happens when a customer makes changes on their orders like quantities, etc.
View 7 Replies
View Related
Sep 10, 2007
Hello,
I'm having a hard time trying to figure out how I should go about designing the table structure for the hours tracking for the database I'm working on. Basically, I have a table with projects (tblProj) that I need to track the number of labor hours worked per month. The problem lies in the fact that each project could have varying time periods, anywhere from less than a year to more than a year and can start and end at any time of the year.
I was thinking of creating a second table (tblProjHours) to store the hours for the project but I'm not sure what the most efficient way of doing this would be. My idea was to create a table as follows:
tblProjHours
ProjHoursID (pk)
ProjID (fk)
BeginDate
EndDate
Month1
Month2
Month3, etc....
I would create a large number of fields/columns, something like Month1 to Month36 just to make sure I have enough months to enter in the hours. This is of course inefficient since some projects would be way shorter than the maximum allowed months set by the table structure and there is always the possibility of surpassing the maximum allowed months based on the table structure. I was wondering if someone had any better ideas on how I should pursue the design. Thanks in advance!
TheMach
View 2 Replies
View Related
Feb 24, 2014
I am trying to track a yearly training in Access 2010 but am not quite sure how to accomplish it. The training originally was just a one-time thing, due within two weeks of a new hire's start date so I currently have it displayed as three fields in my employee table (Due Date [Calculated], Completed [Date/Time], Paid [Yes/No]). Now, employees will be required to complete this training every year (beginning January 1st, not from employee's start date) and I don't know how to accomplish the tracking it.
I need to keep each year's completion date and whether or not the employee was paid. Currently, the "Paid" field is linked to a query that populates a list of employees that have not completed the training. If the "Paid" Yes/No box is unchecked, the employee's name will be listed in the query; once I check the Yes/No box then the employee's name is removed.
What would be the easiest way to track this training for each year? There are several other training that I am tracking but are one-time only events; these too are separate fields in my Employee table.
View 1 Replies
View Related
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
Sep 10, 2012
I am planning my new DB and am contemplating the best design. It will be used for warehouse stock rotation and control of pallets. I want to track each pallet (product/time in/time out etc) to each pallet space within the warehouse. There are a total of 400 pallet spaces or 'locations' as i will refer to them as.
Now, would it be possible for me to have a table set up for each location? Will access object to having 400 tables in my data base?? Is there a limit?
View 1 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
May 1, 2012
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
View 5 Replies
View Related
Jul 19, 2012
I have two tables. The first one is as follows:
MP No (Primary key), Sales, Date Entry, Specs, Email
The second Table is as follows:
MP No, Quantity, Description, DWG, Price
There is a "one-to-many" relationship between the two tables through the MP No field so whenever I go to the first table there is a sub-table for each MP No. Correct?can I create an extra sub table under the sub table already created?
View 10 Replies
View Related
Oct 20, 2006
I'm a relative newbie to the more advanced features available to Access.
If someone could help me or point me in the right direction, I'd really appreciate it.
I have one table that contains companies and associated contact info. Each company can have more than one unique number ID (3 digit alphanumeric).
I want to take this table and run a query to create a new table that will only have one instance of each company name, along with the other contact information. The unique ID's aren't needed in this table (but it would be great if I could somehow toss them in, too).
This new table would then be used to create a form with a combo box containing all of the names to quickly jump to their contact info.
Any and all help would be appreciated. If a query isn't the best avenue for this, then please point me in that direction, too.
Thanks!
View 5 Replies
View Related
Dec 7, 2006
Hi there :)
I'm not sure if I've picked the right forum topic for this... It's not a straightforward table question, and I think the only way I can do what I need is with a query of some sort. Apologies if it should have gone in 'Tables'...
The situation is: I'm creating a database (using Access 2003) in which our secretaries can enter exam marks for students. The difficulty is that there are ten papers (exams), each student's exam is marked by at least two examiners and there are at least 6 examiners per paper. Every examiner also marks more than one paper, but doesn't mark all papers.
So, I've got a table with all of the examiners and the papers they're marking and I've got a table with all the students and the papers they're taking. Because we have to enter the marks in such a way that we know both which mark an examiner gave and (obviously) which mark a student received (e.g. so we can look at statistics for each examiner), I thought the best way to do this would be to run a query that created a new table per paper in which the field names would be: Student_ID, <name of examiner 1 for the paper>, <name of examiner 2 for the paper>, <name of examiner 3 for the paper>, etc until the end of the list of examiners for that paper. The student's number would populate the Student_ID field and then the secretaries would enter each examiner's mark in their named field. Like this:
| Student_ID | A.N. Other | J. Blogs | J.Q. Public | Agreed_Mark |
1234A 72 74 73
2345B 65 68 67
3456C 71 73 72
4567D 52 51 52
Listing the name of every examiner (including those who aren't marking that particular paper) isn't an option (and would have to be hard coded into a table in any case, as far as I'm aware, either in a query or in a table design. This is something I'm loathe to do). Ideally, the secretary would be able to press a button on a form and have all the mark entry tables for all the papers created automagically.
The examiners for the different papers change every year, and not every academic marks exams every year, so this data will not remain static. Hard coding a table with examiner names would make more work for the secretaries. (I want to avoid this as all mark entering for all students is done in a single day. This means over 600 students at 3 marks per student [mark 1, mark 2, and final agreed mark] with time to contact examiners with questions... and only 2 secretaries, each of whom deals with a different section of students).
Because of the nature of the marking, it would also make quite a lot of work for the secretaries if we had to cross reference a generic Examiner_1 field with a different examiner for each paper, so the field name really must be the examiner's actual name.
Can anyone help me? I'm happy to do it with either VB or SQL; I'm not a programmer, but I can usually work out what code does even if I can't write it myself. I should be able to adapt anything presented as long as it's complete and doesn't assume that I know the basics. :) I've also got two reference books to hand: _Microsoft Access 2003 Forms, Reports and Queries_ by Paul McFedries and _How to Do Everything with Microsoft Office Access 2003_ by Virginia Anderson.
Thanks very much for your time. :)
-Jen
View 10 Replies
View Related
Sep 30, 2013
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
View 5 Replies
View Related
Sep 1, 2006
Does anyone have a sample training tracking system / database they'd be willing to share?
Or, barring that, can someone point me in the direction of where I might find one?
Thanks in advance! Looking forward to it!
View 3 Replies
View Related
Sep 12, 2006
Does anyone have a sample training tracking system / database they'd be willing to share?
Or, barring that, can someone point me in the direction of where I might find one?
Bee replied once, suggesting I look in the Samples Databases section of this forum, but I could not find anything even remotely related there.
All replies are gretly appreciated!!
View 5 Replies
View Related
Jan 4, 2007
I have a DB I'm using to track training for assigned people. Right now the training gets put in the DB at the end of the day by going through each persons record and adding the training that they did for the day. As you can see when training 30+ people a day, putting this in the system can take awhile. I'm trying to find a way to do a mass input to cut down time and could use some help with this.
My tbls are as such,
1st Table:EMPLOYEEtbl
EmployeeID
EmployeeName
(Other information)
2nd Table:CLASStbl
className
classID
3rd Table:CompleteClasstbl
EmployeeID
classID
DateTaken
What I like to do at the end of the is select the ClassID/DateTaken items once and then type in a just the 30+ list of employeeID's click a button and append it to the my 3rd table. Of course writing it out like this makes me think it should be easy but for some reason I'm lost. Does anybody have any suggestions?
View 5 Replies
View Related
Dec 31, 2007
I need help finding an existing or making a db. This db does not have much to it, so I am getting frustrated finding/making one.
My company has equipment that is stored in one of three places, customers, trucks of employees, and other(like warehouse, or out for repair). The db should be able to give me a report of where everything is on a given day, and a report of what was in a given customers property during the job(this is not for a single day, but for the duration of the job).
If someone knows of an existing db like this, I would appreciate a link. If you want to help, it is appreciated. At this point I would not mind paying someone to do this for me, as long as it wont cost too much.
View 9 Replies
View Related
Jan 25, 2008
Is there any way in access 2003 or 07 to track when a database was last synchronized with any one of it's replicas?
It would be useful to know and be able to store this in a table.
View 4 Replies
View Related
Feb 12, 2008
Hi,I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.It may or may not be commercial software.If anyone knows of such an available solution, please let me know.(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)thx
View 5 Replies
View Related
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
Mar 26, 2008
Is there a way to track changes made by different users to access tables? For instance if several users update or change data in various records is there a way to track when and what was changed (before and after data)?
View 1 Replies
View Related
Sep 22, 2006
I have read all of the audit trail stuff and searched on my subject but I think my solution could be simpler,
All I want to do is copy the values of the current record on my form into a history table with all the same fields.
Example Form:
[ID], [Machine], [Task], [Task Due], [Task Complete]
I want to put a button on the form that reads complete task and when the user pushes the button it copies all of the details for that specific record they are looking at into my History Table that contains all of the same fields.
This will give me basic history on when each task was completed for a machine and I can access for reporting
View 1 Replies
View Related
Oct 10, 2007
I consider myself an intermediate ACCESS user and can get around it with little trouble with the help of the internet and the help file.
I am trying to design a database that is fairly simple in design, it is for tracking the status of documents at our work center. Basically we have personnel reports that are passed around the different people and different dates.
I have a main form that has the info on the person along with dates that it is due.
Then a sub form that has the date sent and where it went to.
My problem lies in that I want to keep track of the whole history of the document but when i print out a report or export the data to excel to put up in powerpoint I need to have just the most recent date sent only item.
I have 2 tables one with user info and another with the routing info with its own primary key along with a identifier key to link the primary and sub form.
Of course it is easy to create a report with the data to show all the different routing steps but not just the one with the most recent date.
Thanks in advance for the help.
View 10 Replies
View Related
Feb 12, 2008
Hi,
I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?
My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.
It may or may not be commercial software.
If anyone knows of such an available solution, please let me know.
(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)
thx
View 4 Replies
View Related
Oct 19, 2005
Hi:
In query, how can I make table when there is Union
eg.
(SELECT *
FROM table1
UNION select *
from table2) into combine;
It doesn't work.
How can I fix that?
View 1 Replies
View Related
