I am creating a database that tracks current projects for my team at work.
Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)
Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.
I have been using Excel to keep a daily track of when people are in to work, but the number of persons employed has increased and it is taking too much time to manually input data into the sheet.
I am looking at Access and I would like to know if what I want is possible.
The fields I would need are , Name , Date , Time worked(1 for a full day and 0.5 for a half) , Reason worked, and funded by(inside/outside organization)
I would like to make a template of form that has the following options.
1. The ability to select a date (possibly through a calendar selection)
2. Select one or multiple different persons from a list
3. Possibly two check boxes (full/half day) - that when selected input a 1 or 0.5 into the database
4. A text entry for reason worked.
5. Another check box for inside/outside funding that reflects the time worked in step 3 and that can be queried to see how many total days funding was given.
Just curious as to what others are working on. Here is what I'm in the middle of and what the basic purpose is:
It's a db to track parts we swap with our customers. It's primary table is on a sql sever so that the anyone in our company can view only the data via DAP's. The other tables are in the .mdb on the file sever. I plan on having it up and running in the next week or so. It will be used (add/edit,etc data), by approx 8-15 users in three different buildings off a file server share folder...
See screen shot attachment.
I am new in Access 2010 and need table design schema. I am creating a database to track budget changes within our organizations.
1. We have 3 division (West, East and Central).
2. Each division will have 5 Organizations (Office, Campus, Operations, IT and HR)
3. Each Organizations will have multiple sub-organizations
4. The budget start with the sub-organizations - which is break down by cost center and sub cost center.
This is the design I came up.
tblFiscalYear
tblDivision
tblOrganizations
tblSubOrganizations
tblCostCenter
tblsubcostcenter (budget table)
tblDivision --1 to Many --> tblOrganizations
tblOrganizations --1 to Many --> tblsuborganizations
tblsuborganizations --1 to many --> tblCostCenter
tblCostCenter --1 to many --> tblsubcostcenter
Is there a better way to design this?
What I'm doing wrong with the chart in the attached DB? I need just one chart for each team but I'm getting 5 times as many charts as I want.
I'm using Access 2010 on Windows 7 & Vista
I Have a table that contains the fields: CAT, CHAPTER, ID, someSrting and Completed
CAT, CHAPTER, ID are numbers and Completed is true/false. If I mark a certain entry as Complted (true), I want all the records with the same (CAT, CHAPTER, ID) as the one I marked to be updated to Completed.
For example, If I marked the entry cat:1 chapter:1 id:1 as completed (true), I want all the entrys that have cat:1 chapter:1 id:1 to be marked as completed (true)
the code I wrote is:
Code:
Private Pub Completed_check_AfterUpdate()
Dim myCC
Dim myCat
Dim myChap
Dim myID
myCC = Me.Completed_check
myCat = Me.CAT
myChap = Me.CHAPTER
myID = Me.ID
CurrentDb.Execute "Update [my Table] " _
& "SET Completed = (" & myCC & ") " _
& "WHERE CAT = (" & myCat & ") And CHAPTER = (" & myChap & ") And ID = (" & myID & ") ;"
End Sub
I'm running the code (my clicking the "check box") and nothing happens, I was thinking that maybe I defined the after "WHERE" statement wrong, and there are 0 entrys changed..
UPDATE: after removing the
Code:
"& "WHERE..."
row, the code does update the entire table, so I have a problem with the syntax of the sql..
I am attempting to develop a DB to track hardware assets, e.g., servers, switches, storage devices, etc... I am at the very beginning of the development/design process. I haven't gone beyond the paper stage yet. I am trying to figure out how I would set up the main Asset table so that when I put in a particular vendor, the model field will list models only for that specific vendor. i.e., when I put in IBM in the Vendor field I will only see IBM models in a drop down for the Vendor_Model field.
I'm pretty sure this will involve a Lookup field. But, how do I restrict the lookup of models. Would that be with a query?
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
I'm having difficulties creating a sql statement which updates data in another Access database from a current database.
I'm able to do an insert and delete statement just perfectly, but not an Update. :confused:
For example, here is a dummy sample (which does not work):
Update tbl_test1 IN '\C:Tempabc.mdb', tbl_test2
set tbl_test1.Name = tbl_test2.Name
Where tbl_test1.ID = tbl_test2.ID
I tried a couple of variations, however, I keep getting an error.
Also, I don't want to create a database link (due to the sheer complexity of my project, which I'll spare you the details..)
Does anyone know how to do this? I searched the forums and came up with zilch! =(.. Help??!
I was wondering if it could be possible to populate all the players from a team to a subform for a softball statistical database..My tables are currently set up as follows:
Player: (playerID, teamID, playername, etc)
Team: (teamID, leagueID, teamname, etc)
League: (leagueID, name, etc)
Games: (gameID,Date,Time, teamID 1, teamID 2)
Season: (seasonID)
HittingStats: (playerID, gameID, seasonID, PA, 1B, etc)
Currently i have a set of dynamic combo boxes. After the user chooses which league the team is associated with, the Team combo box re-queries to the teams specific to that league. From there i was wondering if it is possible to populate a subform with the players that are specific to that team picked within the combo box. The subform is for the teams hitting stats, which would populate the subform with all the players on that team, allowing the user to just input the stats and not worry about inputting every single playerID.
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code:
=date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
I have a query called "Program Status Count" that keeps track of the current status of various programs. There are three statuses: Closed, Deficient, and WIP.On the Query, I used "Total:" row and have two fields: StatusID field set to "Group By" then in the next column I have the same StatusID Field set to "Count" (it auto-renamed itself to CountofStatusID)...which on the query does what it is supposed to do:
Closed, 2
Deficient, 1
WIP, 1
When I go to the Report Design and try to make a Pie Chart (or any graph) it asks me what query I want to use, so I point it to the Program Status Count Query and select the only two fields I have in the query.This is the Row Source Code:
SELECT [StatusID],Count([StatusID]) AS [CountOfStatusID] FROM [Program Status Count] GROUP BY [StatusID];
Sometimes, it will show the appropriate fields but then it will disappear in Report View, switching to Print Preview does not fix the issue. Actually, it often deletes the correct information in the chart and use the PK autonumber instead of the words.Othertimes, the default data shows up and nothing I do changes it. I have even done the copy/paste of the query table in the report (doesn't work when the graph needs to update.
Im trying to create a database so I can Monitor grocery items from different supermarkets in my area.
I have been doing this on Excel for a while now, and its time to do it properly.
The only issue I would have is when I enter data.
I want to be able to track the price changes on items. I have my own.
So for example. Each time i enter the price of an item, I want a time stamp with it. (being the date when i entered the item, should be automatic).
I was wondering if it was possible to link random external tables to the Active Database through VBA. I would like to run the code that would open up a dialog box that would let the user select the database as well as the tables within that database that the user can select to link to. I am able to select the database and but not able to select the actual tables. The tables will be random so I can't make a constant statement for a specific database.
View 1 Replies View RelatedI have a split database. I have just created anew table in the back end and created a relationship to an existing table. All done in the backend.
I have opened up my front end and imported the back end end. But when I check the relationships in front end neither the table or link is there.
Hi guys,
Im fairly new to MS Access and have recently created a simple database to track the details of people in my department.
The database is on a shared drive and the idea was that when the details of the individual change, they open the database themselves and edit the appropiate field.
However, is it possible to track the changes that people make over a certain period (ie every month).
Thanking you in advance,
Jonny
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.
I've got a form with several date fields (in Medium date format), with each field bound to fields in a table, where also the dates are in medium format.
I also have the form set up where I can enter dates manually, or I can double-click and pick dates from a calendar form, or I can click on an icon and pick dates from the calendar form. Each date goes through the same bunch of processes (i.e., queries and code).
My problem: Some dates work, and some dates don't. On some date fields, I can key in a date, exit the form, re-open the form, and my dates show up (since they were processed into my table.) However, some other date fields do not get processed! I enter dates into these fields, exit the form, re-open the form, and there are no dates!!
I have checked all fields in my table..... they are all date fields, of medium date format. Same for my form.
Why would some dates work just fine while others don't work? If anyone has some suggestions I could try, I would be grateful. What might I look at in my application?
Why doesent this work
aa = Me.Due
aa = DateAdd("m", aa, Date)
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < " & aa & " ORDER BY [Tasks].Staff_Name;"
the sql dosent display any results!!!
Trying to modify this calendar database to track employee leave. I need the calendar form (frmCalendar) to show all employee leave on the calendar using the "Show All" command button on click event. This works if I specify a uID (UserID from employee table) in the code, but only for that specific employee. I need all employees at the same time up on the calendar so I can see if there is more than one employee off on any given day.
Events or leave is populated on the form through "Private Sub DisplayEvents" and the mdlCalendar module.
Using Access 2010
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
View 7 Replies View RelatedAll; using Access 2010. I need to have some kind of way to track changes based on different field name.I have a client table with field data like Fname, Lname, SSN, Address, Zip, Phone, DateOfBirth, Current Rate, TotalRate, Status and Importdte. I have an import into the database every month and I update this table with data that is new based on the SSN with an append query. The import also has the names of those that exist in the client table until their status changes. In addition to appending new data every month; I would like to append any changes the existing client has. For ex; If an existing client has an address change, I want to append this data to the table but keep the existing address and have a Change date equal to the Importdte. In essence; I want to build a kind of history table that when I pull up a client; I can have something like a tab on a form with the changes and when it happened.
View 6 Replies View RelatedI'm developing a database that tracks monthly metrics of employees. These employee are at several different locations.
Here's what I would like:
A table of the employees and their location.
A table of the metrics.
A form that you can select an employee and it would autofill the location and then you could fill out the metrics.
the ability to change an employee's location without it effecting past records.
A report, by month and location of the employee's metrics. (pretty sure I can do this on my own, just can't get to this point.)
I've tried to use a auto lookup query but that then changes the employees location on past records.
I have created a relational database for my local running club.
It has 2 tables, 1.information on athletes which includes name, athlete's club etc as well as an individual athlete number which is the primary key. and 2. A positioning sheet with the place as an autonumber (This is an autonumber as the first details enter will be first place (autonumber=1), second being the second autonumber (2)), athlete number and time field. There is also a query which merges the information (including club) from the two tables to make up a results sheet.
In these competitions there is a team event this works by the first 4 people of any one club to come over the finish line. The positions of these athletes are then added and the team with the lowest score wins. The resulting information should be calculated for all teams in the race. There can be an A, B, C team etc for the clubs, meaning that the first four over the line for a club are team A, next 4 are team B etc. It also needs to cancel out any teams with less than 4 people. Meaning i f9 people run for the striders and place 1, 5, 6, 7, 9, 10, 13, 15, 25 then Team A is 1, 5, 6, 7 and Team B 9, 10, 13, 15 the runner who came 25th is cancelled out. Athletes from other clubs place inbetween (2, 3, 4, 8...etc)
My problem is how to create this list of team results in the Access database.
I'm trying to design a billing and invoicing database and can't seem to figure out how to link the tables and track the payments made by customers. How can I proceed to track the payments and make payments applicable to certain line items or invoices? If the line item hasn't been paid, it needs to show up on the next invoice and be included in the total due.
View 14 Replies View RelatedIn my Query I have a Date Received field and a Date Approved field.
I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.
It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.
How would I go about doing this? I would need to add a new column to my query?
What would I put for the field and criteria? What kind of expression do I use?
Any help will be appreciated, thanks