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?
I have a query that formats data into a list of personnel who are coming due for refresher training. The query is formatted to provide me all the details that I need to create an outlook task, and assign it to the person. I tried creating a button on my form to create a record set of that query, and then create / assign a task to each person based on the values from the recordset.
Code: Private Sub SendTasks_Button_Click() 'Create the recordset and set it to the appropriate query Dim rst As DAO.Database Dim rstData As DAO.Recordset Set rst = CurrentDb Set rstData = rst.OpenRecordset("SendTasks_qry")
[Code] .....
Basically, the compiler stops at "Dim myOlApp As New Outlook.Application" with the error: "Compile error: User-defined type not defined"
I've been looking at all the fine db samples and advice provided in this forum and they are giving me a huge boost in my new job as a DB Admin. :D
Since this forum has a very active group and you guys know and done it all, ;) I thought I would ask if anyone out there has done or could give me an idea on how to buid the following db:
I need to create a db that will keep track of a specific item from the moment it comes in 'til it goes back out. This could be weeks in the process.
What happens is that this is a program where people donate old worned out wheel chairs and they get totally overhauled. As the chairs come in they need to be tracked from start to finish to see where they came from (Somewhere in the USA), what process it took to overhaul it (ItemID and Current location [Storage, Repair BIN, Repair Table, Completed Tabe, Outbound Table, En Route to Destination, etc] and status [For parts, fixable, paint room, repair room, completed]) and where they were donated to, a city in some foreign country (Africa, Asia, Mexico, South America, Etc.).
If someone has something I can use would be great, but if not any idea on how to do it best, would be greatly appreciated.
I have a database which is working ok where i have a set of tasks in a table with a bunch of irrelevant fields. On creation of a task i assign users to those tasks into a multivalue field(this is all fine).
Using a query i can report to each user what tasks they are assigned to easily.
To make the database more complex i would now like to assign these users to a task and then allow them to assign there portion complete (but not the whole task complete) but i can not assign a bool variable to a .value (can i?).
My thoughts are my database is just not set up for this by using the multivalue field to hold the assigned users?
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.
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?
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
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?
We sometimes make an error in entering a stock code and have to change the code.
Our main table is = Investments01_tbl Other tables that use the same stock codes from the above table are = Alerts_Stocks = Investments01_tbl_SubForm
The table = Investments01_tbl_SubForm = can contain the same stock code many times.How can we create a query that can be used to enable us to change the stock codes in the 3 tables at the same time?
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.
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'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)
I'm creating a database for a home builder to track a lot of info, but mainly: house info, prospect info, actual customer info, and contract info.
My main tables are: tblCustomers CustID PK (a bunch of customer fields)
tblHouses HouseID PK (a bunch of house fields)
tblContracts ContractID PK CustID FK HouseID FK (a bunch of contract fields)
The releationships are as follows: One house can have many contracts One customer can have many contracts
I thought I could use one table, tblCustomers, to track the prospect info and actual customer info, as so much of it will be identical (technically someone should be entered as a prospect before they can be a customer, and all info will be the same for both except for extra information fields for prospects, but I always want to be able to refer back to those fields) and I would know a prospect became an actual customer because at least one contract in the tblContracts would have their CustID associated with it.
Is it acceptable to have two foreign keys in tblContracts? And am I missing something that should exist between House and Customer? House and Customer are only releated when there is a Contract, so it is implied (I think) that a Customer can be associated with many different houses through many different contracts. Is that the right way to look at it?
I've come up against a wall regarding linking two forms so that the second form displays the data associated with what was showing on the first form. If I'm good here with the tables I suppose I'll post my detailed question in the forms area.
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
I want to comine the following queries into one so that I can alter the parameters from a webpage.
I have a users table that has the department in and I have a TimeTable Table which has dates and option codes.
I need to select everybody in a department in the example it's Logistics. I then want to link these people to the timeTable Table based upon a field. The problen is that I want to select only a specific week. I can do this using two queries but that isn't going to work in ASP.
The Results should be as follows:
Person Date Option A B 01/10/2005 1 B 02/10/2005 2 C 01/10/2005 1
The two Queries look as follows:
SELECT TimeTable.* FROM TimeTable WHERE (((TimeTable.Date)>=#10/24/2005# And (TimeTable.Date)<=#10/30/2005#));
The above is then saved as Query1
SELECT Users.*, Query1.*, Query1.TimeOfDay, Query1.Date FROM Query1 RIGHT JOIN Users ON Query1.UserID = Users.ID WHERE (((Users.Department)="Logistics")) ORDER BY Users.Surname, Users.Firstname, Query1.TimeOfDay, Query1.Date;
Hi there, i have a database i am setting up with 4 tables and am trying to think of the best way to input new data into it. All the tables are linked and its all based on printers. When a new printer is brought if it exists in the database then thats cool it just needs a Printer number assigned to it and the database will do the rest looking up printer model and what cartridges it requires. But if u add a model for a HP printer for example you need to add the model number to the tblink and create new cartridges and use the cartid to link the modelID i hope u get the picture!? lol there is a pdf i have created using onenote with screen dumps and comments about what i need it to do. If anyone has some advice or help it would be very greatful! Thanx mike
Here i have a situation i have a table where like below
Name | orderno | mail no | contact No | --------------------------------------- a |123 |5555 | 553453 | b |321 |8569 | 52353 | a |123 |2344 | 553453 | c |143 |567 | 553453 | d |173 |6787865| 553453 |
But i need a separate table where the entry of all the entries where a is coming only once.
I am attempting to set up a saved import procedure in an Access database that will be run programmatically using docmd.RunSavedImportExport. The source document is an Excel file.My goal is to trap any import errors caused by inconsistencies in the source data. I'd also like to provide the user with some small amount of feedback on what is causing these errors.
My initial thought was to make use of the "File_ImportErrors" table that is generated following an unsuccessful import. *Not once* has this import procedure consistently generated an error table - sometimes the table is created, sometimes it isn't.
I have tried changing the file format of the import file to comma- and tab-delimited files, respectively. Both of these formats do consistently generate an error table, however none of the error values in the table are accurate - it reports a slew of type conversion errors for fields that are completely consistent and unproblematic, whilst completely overlooking conspicuous errors from text strings in date fields.
I have tried the same import, both procedurally and manually, using all available Excel and text-delimited formats, with the same respective results.I notice that nearly every thread on AWF pertaining to error tables discusses how to delete them, rather than make effective use of them. I suspect this is perhaps why?
I've been doing some reading on multivalue fields and it seems to be exactly what I'm looking for. My question: Is there a way to use the multivalue field without making it a lookup? I need the user to be able to put multiple values in a field but can't limit them to a list. The field that needs multiple values happens to be for SKU numbers from a large department store franchise.
One work around I've come up with is to have a pop up that asks for the separate sku numbers so they can be used for the value list each time, but then the user also would have to select those from the value list in order to enter it.... too many end user steps.
I want to avoid making 10-15 different fields each for SKU number, Item, and cost... I also need to avoid having to populate my db with the department store's full list of products...
I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.
I have a form that simply lists the items: DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE: If IsNumeric(DEPARTMENT_NBR) = False Then If DEPARTMENT_NBR <> "00" Then MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly DEPARTMENT_NBR.SetFocus GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks) End If End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
