I am designing a database to store volunteers and what particular function they are volunteering for. I have about 17 areas of volunteering tasks (i.e. Fundraising, Teacher) and at least 3 subcategories for those tasks (i.e. candy fundraisers, nursery school teacher). I want a form that you can enter the volunteer's info and then choose as many areas of interest and as many subcategories. I guess this would be done with checkboxes? Right now I am putting each volunteer task in seperate table (ie. Fundraising table, Teaching Table) and then trying to use combo boxes. But I want the person to be able to choose one or many activities.
Can anyone help me design this or give me a suggestion so that I can query or create a report to list the volunteer and what activities they want to volunteer for. I really appreciate any help I can get at this point. My email is ebbr820@hotmail.com or I will check back on this forum.
I have two forms, a main form frmProperty and a secondary form "Training Details" you access the training details via a button on the frmProperty form. This is the code for the button.
Private Sub Trainingbtn_Click() On Error GoTo Err_Trainingbtn_Click
Dim stDocName As String Dim stLinkCriteria As String
I noticed however that whenever the user enters data no matter what the "PropertCode" of the main form it was assigning it to PropertyCode = 0 :(
So as a temp fix i have inserted the following code into the GotFocus of a text box that is filled in. This code did not help in the Form_Load section...
Each month I receive a new excel spreadsheet (alpha Roster), Last month I created a form so that I can track certain things off of the Table (spreadsheet), I just received the new Spreadsheet for this month and was wondering if I can use the existing form and how hard it is to link it to the new spreadsheet? Is that even possible? I don't want to have to re create the form every month, I would like to just bring the new alpha roster in and be ready to work.
I have what should be a really simple database to build. In table tbl1PlanHdr is the header item. Many items in tbl2MaintItem will then be allocated to each header. MaintItems items will be allocated to more than one Header.
I want to set up a form that has the tbl1PlanHdr as the main part of the form and tbl2MaintItem as the subform. Possibly as a datasheet, or continuous form.
I need the ability to update items in the subform back to the orginal table.
I have a relationship of one to many from the tbl1PlanHdr to tbl2MaintItem using the primary key in tbl1PlanHdr.
But should this be a one to many from tbl2MaintItem to tbl1PlanHdr
The end result required is to be able to get a data dump of all the tbl1PlanHdr items with the corresponding tbl2MaintItems items.
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are, Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records. Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:
There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.
The whole technical process is this: 1. A csv file is imported into a temp table 2. A temp table is created and named "_ImportedSKUS". 3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs". 4. Then the temp table, "_ImportedSKUs" is closed and deleted.
I have a delete command at the end of the sub, which goes like this:
DoCmd.DeleteObject acTable, "_ImportedSKUS".
But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.
Hi Everyone, I need help in selection of primary key and designing of a tables. I am having a table called Current trailers with Trailer number as a primary key. I have similar table to current trailers called history table in which the trailers that left the yard are stored. As a trailer can enter and exit the yard couple of times in a week, its not letting me to have duplicates in the history table.So can anyone help in selection of primary key for the history table. Or can anyone tell me how I can have duplicates in a table.
I’m having trouble defining Relationships I’m thinking I need a “Junction” Table and I have tried looking at the Orders.mdb but it hasn’t helped (I’m sure I’m just missing something) I just don’t see how it works. If at all possible please don’t just give the info try to help me understand so I can get the answer myself. Here is what I have: Far table: FarNumID (PK) > autonumber FarNumber > Text “224-10C” FarTitle> Text
FarParagraph table: FarParaID (PK) > autonumber FarNumID > Number FarParaTitle > Text FarParaText > Text
AC table: ACNumID (PK) > autonumber ACNumber > Text ACTitle> Text
ACParagraph table: ACParaID (PK) > autonumber ACNumID > Number ACParaTitle > Text ACParaText > Text
1. Each FarNumber can have only 1 FarTitle 1:1 Each FarNumber can have many FarParaTitles 1:Many Each FarNumber can have many FarParaText 1:Many 2. Each FarTitle can have many FarParaTitle 1:Many Each FarTitle can have many FarParaText 1:Many 3. Each FarParaTitle and have only 1 FarparaText 1:1
I am looking to set up tables in such a way that will allow for the concept: If Variable 1=A and Variable 2=B then Output of 3=C
For Example: If the House is "Blue" And the Man drinks "Beer" Then his pet is a "Dog"
If the House is "Green" And the Man drinks "Vodka" Then his pet is a "Bird"
NOTE: There is no equational relationship between the first two variables to get the 3rd output. It is simply a lookup table. In excel you would have the color house down the left, the drink across the top and in the cells would be the type of pet. But I am struggling with how this can be made to function within Access since I will want the user to obviously be able to pick the first two variables and have the "answer" spit out.
I will give you an overview of what I have. I have a database that records sites in Blackburn and each site has a unique ID (RTP_ID). Each site has 10 objectives with 25 Measures in which the sites are scored against. Objective 1 has 2 measures Objective 2 has 8 measures Objective 3 has 1 measure Objective 4 has 1 measure Objective 5 has 1 measure Objective 6 has 3 measures Objective 7 has 2 measures Objective 8 has 1 measure Objective 9 has 2 measures Objective 10 has 3 measures
Each site has the same objectives and the same measures, but the scores for each are individual.
I don't know how to create the relationship between them so that each site can have 10 objectives and 25 measures (with scores) individually. At the moment, I have them connecting as SiteDetails.RTP_ID > Objectives.RTP_ID and Objectives.OBJ_ID > Measures.OBJ_ID but it doesn't work, it thinks that the scores apply to every site.
Please help me, I know that this may not make sense without seeing the database, but unfortunately it is too large to attach.
I have almost finished my current database but I was asked to create a log table/log file that would list changes made to every record. Now my current database don't allow duplicate records, so any advice pointing me into the right direction will be helpful. I have ran through the search area and found nothing that I can use. Can any one help me out in this specific problem. I picked up a few books and none of them give examples of such things. Thanking you all in advance...
I am a girl guide leader trying to make a faster way to keep track of my unit's completed badge and program work. I have made tables for every area I need (I started with one table but it was too large to change into a form).
In total I have:
- Basic Member table (main information like their name and how many years they have been in, whether they are still in the unit, etc) - Program area tables - Interest badge tables
What I want to do is be able to "add/edit" members from the main form, then go to all the sub forms for their program and badge work -- problem is, how to link them!
Could someone please share their opinion with me on the following:
I have a database with a Table which stores People's details, e.g. ID, Name, Surname, ...., etc (e.g. tblPeople) .
Now I have people filling out a questionnaire, of which the results I want to save in an Access table. Each person will fill out this questionnaire only once. The number of fields I will need to accomodate the answers to questions asked, is around 120. For example, an answer to Question1 will appear in a field called Q1. I know that the limit for fields in a table are 255, and my total fields in tblPeople so far are about 20.
Should I keep Q1 and all other fields in the same table as tblPeople, or create another table to hold all answers and create a one-to-one relationship between tblPeople and tblQuestions&Answers ?
Can someone give me a tip, in particular if this will cause performance problems?
I am designing a database for my company that keeps track of new classes and new courses created. I have a form for each with the create a class form taking the course title from the courses table/created course from the create a course form.
Here's my dilemma. I want to use cascading combo boxes for class location, facility and room but I think my table structures and relationships may be incorrect.
Here is a list of my tables and their fields:
tblClass ClassID (PK) CourseID (FK) InstructorID (FK) StartDate EndDate StartTime EndTime ClassMin ClassMax Materials (yes/no) MatNotes EmpID (this is the ID of the employee who requested a class creation) LocID FacID RoomID LogID (Logistical Coordinator ID, if applicable)
Course CourseID (PK) CourseName more...
Employee EmpID (PK) FName LName more...
Instructor InstructorID (PK) FName LName more...
Logistical LogID (PK) FName LName more...
Locations LocID (PK) Location
Facilities FacID (PK) Facility LocID (FK)
Rooms RoomID (PK) Room FacID (FK) LocID (FK)
When I run a query with class, locations, facilities, and rooms no information shows up. If I run the query with the relationships between facility/room, facility/location and room/location removed (leaving the only relationships between class and these three) it works. Why is that? Shouldn't there be a 1-M relationship between Location, Facility and Room? Or, should there be a M-M relationship and I should create tables with each PK?
I apologize for the long post but this has become quite frustrating (despite the simple logistics).
I manage academic papers whom are written by multiple authors and reviewed by multiple reviewers. Currently, all the data is stored in one big table and I'm not able to get any statistics or real tracking out of it.
I have set up a test DB with 3 tables so far:
Main Table_ID -auto sequential key CORP_ID -internal paper id STATUS -open, overdue, accepted, rejected REC_DATE -received date MOD_DATE -record last modified date TITLE AUTH1_ID -linked to Author.ID AUTH2_ID -linked to Author.ID REVR1_ID -linked to Reviewer.ID REVR2_ID -linked to Reviewer.ID
Author ID -key field NAME DESIG -designation EMAIL PHONE
Reviewer ID -key field NAME DESIG EMAIL PHONE RATING
My goals are these:
Create a module that manages the authors. They are usually the same group of people. Each author should only appear once.
Create a module that manages the reviewers. Also, usually the same people (but different than authors). Each reviewer should only appear once.
Create a main form that allows the end user to add a record (paper), assign author(s), assign reviewer(s).
Create Query/Report that would then be able to: - track authors and their papers - track reviewers and their reviews - as well as track papers and their authors/reviewers
When I create subforms, they appear to be 'backwards' meaning that the Author form tends to be the main form and the Main form tends to be the subform. I think this is b/c the Author.ID field is the key field, whereas the Main.TABLE_ID is the key field, but not relevant for other than keeping things straight, nor is it related.
Can someone point me in the right direction?
Alternatively, has someone created a database like this already that I can buy? :)
Firstly, I want to admit that my knowledge about data base construction is fairly basic. I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country. In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields. What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations. The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project? I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables. Many thanks in advance. Niels
Hi, based on previous advice I had merged my customers and owners into one table and selected whether a client was a customer, owner or both via a lookup table.
This is working fine, however I must assign a rep to each owner, I'm not quite sure how to do this, I need to be able to assign a rep to an owner in the clients table, I must also make sure than a rep can not be assigned to someone who is just a customer obviously.
I have attached the database (http://jonroberts.redirectme.net/database.zip), hopefully that will make things more clear.
The Lookup table has two fields containing values that are needed: Description and Amount ($). In the table that uses the Lookup, I'd like to have both values shown, but have a dropdown just once. In other words, when the user selects a description (the dropdown shows both the description and amount), can the amount be inserted into an Amount field as the description is done currently.
Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent
Hi experts. i wrote this code that supposed to create table inside the access db but when i run it i get this error:
compile error: user -defined type not defined for Dim db as Database
Itt is a command button that calls CreateDatabase. I be happy if some one help me fix this. Thanks
My code: Option Compare Database
Public Sub CreateDatabase() ' Routines to create the tables. CreateTablePlayers 'CreateTablePenalties ' Routine to create relations for the tables. CreateRefInt End Sub
Private Sub CreateTablePlayers() ' Local variables Dim db As Database Dim tbl As TableDef Dim fld As Field Dim idx As Index Set db = CurrentDb() Set tbl = db.CreateTableDef("Players") ' Adding fields Set fld = tbl.CreateField("playerno", dbInteger, 0) fld.Required = True tbl.Fields.Append fld Set fld = tbl.CreateField("name", dbText, 25) fld.Required = True tbl.Fields.Append fld
' Add Table to the database db.TableDefs.Append tbl End Sub
' Adding Referential Integrity Private Sub CreateRefInt() ' Local variables Dim db As Database Dim fld As Field Dim rel As Relation Set db = CurrentDb() Set rel = db.CreateRelation("PlayersPenaltiesRel", "Players", "Penalties") rel.Attributes = dbRelationUpdateCascade Set fld = rel.CreateField("playerno") fld.ForeignName = "playerno" rel.Fields.Append fld db.Relations.Append rel End Sub
Private Sub Command1_click() CreateDatabase End Sub
I have this declaration in a module called Global Code Option Compare Database Public currentCustomerId As Long Option Explicit
There is one report that simply prints a the firstname of current customer and the amount of each of his invoices. The current displayed customer's ID is always copied into the variable currentCustomerId. Below is the SELECT code of the data source of the report. But whenever I open this report it asks for currentCustomerId. Is my decalartion not global enough?
SELECT tblCustomers.FirstName, tblInvoice.Amount, FROM tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustomerID=tblInvoices.CustomerID WHERE tblInvoices.CustomerID=currentCustomerId;
Note: I chose to save the current customer's ID in a global variable because, for some reason, when I select the report the Customer form loses the current customer record and goes to the end of the table as if creating a new customer record. This would not be an issue as long as I can retrieve the value in a variable.
Ok...I have an issue that I don't know how to resolve. I have a table called 'orders' in the orders table there is a place for " quantity', 'colours' and per unit. The cost per unit is based on quantity and colours. For example: 12-35 shirts 1 colour are $2.85 per unit. 12-35 2 colour are $3.50 and so on. I would like to be able to put in the quantity and # of colours and the Unit price fills in a field. I don't know how I can accomplish this.
I have created an Access database application that is stand alone for each account manager/client at our business except for one table that I want shared as a source of data across all separate databases that are created.
I've investigated automatic Database Splitting and this works, except I only want the one table to be split and the others remain unique to each database.
I tried exporting the table in question to another Access database and then linking the table into each other database, this seems to work except that referential integrity can't be set up as it was. Is there any way to keep the integrity between the external database and the databases it links to?
I'm using 2 databases. The first is my working database (LiTiMA.mdb) the second database (LiTiPA.mdb) is just for the password table. There is no link between the 2 databases. But when I open a form for editing the password in LiTiMA, a connection need to be created between the 2 databases. If the form is closed the connection need to be removed.
I have an access dbase trying to link a paradox table. I have Office 2003 and BDE 5.01 was installed on my machine. When I try to link to a paradox table its giving me an error "Unexpected Error from xternal database driver (11265)". On the other hand, I have Office XP on my laptop and BDE 5.01 installed, I tried to link to a paradox table and it works. :eek: :confused: What can I do to link to a paradox table using Office 2003? :confused:
