I am trying to add a new dimension to my database and have a table for 'Committees'. There are several diferent committees overseeing different tasks, each commitee will have a CommitteeID, description, Date formed, Date disbanded and field called 'office'. The idea was that the DB user could open a form and amend or add a new 'committee' electing a chairman(Office) secretary, Vice Chairman etc. The members elected to the committee can only be members of the club i.e they already have a membership number, and their data is stored in the tblMembers. I tried using a sub form of committee members but cannot get it working I keep getting problems with duplicates etc. Members are 'autonumber' as is committee ID bearing in mind you can have 1 member on several committees, and each member can take a different office after a period of time etc. Im getting confused now which is the best way to set up the tables and indeed how many tables I really need to store committee, committee members etc. Ideally I would like a form that you can use record selectors for to find a committee and have a page (tab) to enter members relating to the committee displayed on the first page. I know this is about forms but I need to work the tables out first.
I have made severa databses before but im trying to make this one perfect and im curious, when defining tables should you include atributes about an entity that are static?
Its kind of hard to explain what i mean but for example in a shoe shop a staff member gets commision on what they sell (ie 5%) so when producing the table should a coloum be reserved for commision or should this be left out as it will be calculated later on in a querie and if so should i state this in the design section of my database?
any opinions would be greatly received as i have been reading books on sql and ERD's for days and havent been able to obtain any answers
I have a couple of questions for someone who has a moment...
I'm a recruiter for the Marines and have been tasked with creating a way of tracking "sales" and "returns". I think I need 17 seperate tables (12 months, 4 qtrs and 1 annual) to store the data. How would I update the names and locations of each recruiter from one form? I apologize if this is a newby question, i tried to search but couldn't find anything that helped me.
In case it helps the sales would be in 10 differant categories and returns in 2.
I have created 2 tables TBL_ObjectList contains data for objects in the night sky e.g. IDMessier_IDNGC_IDCommon_Name Type DistanceConstellation Apparent_Mag 1M1NGC 1952Crab NebulaSupernova remnant6.3Taurus9
TBL_Observations contains observing data where the Object field looks up the data in TBL_ObjectList e.g. ObservationIDObjectDateNotes 1M122/11/2007blah blah
I created a form FRM_Observations with a combo box to select the object e.g. M1 and it fills in the rest of the data for the object in the form.
My question is: How does this then relate to TBL_Observations? When I switch to a new observation record the object selection stays at the one for the previous record.
I know this is probably basic stuff but I just cant see it.
Is there a simple online "how to" for this stuff? as I just cant seem to get to grips with it.
Hello, ive done a bit of access before but ive got myself a bit confused on my next step.
I have a database of books that i loan out. I have a table containing the books instock. I want to be able to loan out book s and reserve books. The problem is whats the best way to do this? Should i have a form that allows me to click a button that takes the book out of the books instock list and adds it to the reserved list? The problem i see with this is when all the books had either been loaned or reserved then the books instock list would be empty and nobody could reserve anymore books which would be a vital floor. Does anyone have any ideas?
In looking through the forum I am not finding what i need to do. I am sure the answer is on here, but perhaps I am not wording my search correctly. I apologize in advance if the answer is on here and I don't see it.
My problem is I don't know how exactly to begin.
I want to keep track of 11 rules and that could have 1 of 4 different things (violations, transfers, letter sent, referred out) could happen to each of those rules.
For example rule #1 I could have 10 that no violation occurred, 5 transfers, 1 letter sent and 2 letters sent as seen in the attached picture.
I created tables for each of the following: violations, transfers, letter sent, referred out.
Each of those tables has a fieldname for the 11 rules. How do I link all those tables together?
Hopefully I explained myself and i appreciate any help that I can.
Order# customer date/time order received date/time order due courier (the order might be shipped via two or more different couriers) dept (the order might be sent to three different depts.)
the order will be for making photocopies of different sizes and different width
If I need to provide more information, let me know. Any help would be appreciated
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
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, I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me. Actually, I want to make a database regarding ultrsound scan examinations of patients. I have five tables. 1. Patients. (patinetid*, patientname, age, sex, address, contact no) 2. Physicians. (physicianid*, physicianname, speciality, address, contact no) 3. Scans. (Scanid*, scanname, charges) 4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges) 5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table?? All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship. So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
In a simple Access 2003 database that has two table. A contacts table and a Report table.
I would like to be able to prevent the deletion of any contact that is still being used in the Reports table. The problem is that they are in a backend of a linked database. So the enforce referential integrity doesn't work. Plus I don't think I would like it anyway as it seems kind of dangerous to the reports table.
Is this possible to do what I need through the use of form coding?
The interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
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 have written the following query for use with my Automatic Weather Station
10 Min Wind Direction: Val(IIf((([Date]="11/07/2006") And ([Time text]>=1600) Or ([Date]>"11/07/2006")),[CR10X AWS]![Field6],""))
Basically I had some data from 1300hrs on 7th July until 1600hrs 11 July that was wrong and I needed to 'null' the data reading - the above works fine showing a value of '0' for the incorrect data between these times / dates and all the correct values are displayed since then however, since 01 August the field nows shows 0 again - any ideas why ?
I have several queries in access that are linked to excel and when i update the excel worksheet the query information appears in a tabular format, which is fine. I also ahve a master table in excel which is supposed to update using the info imported from access. This table is based on fixed cell refernces but whenever i update excel with the access info, the majority of the excel fixed cell refernces change to random numbers? Any ideas?
My question is essentially about the saving of information into the underlying database. I have been searching the forums and havent had any luck in finding what I wanted. There were a number of similar threads but I couldnt find any replies which could really point me in the right direction.
1. I am trying to prevent the underlying data from being changed until I click the save command button. It cant seem to work since even before I click the save button, the record is already written.
I tried using Before update cancel = true but that prevents me from moving away from the control. Also, I tried using edit locked but that still records the changes once i made it on the form.
What is the most common way of people saving records upon confirmation? And how do you prevent automatic recording? Any advice would be very much apperciated.
I have done a bit of reading and research on the 'tag' property in forms and tried to set some code. Now I am all muddled as to how to do it and what comes first?
What I am trying to do is if the condition in one field 'time required' is yes then I would like a whole lot of bound controls (fields) not to appear so that the user cannot enter any information by mistake.
The code is below;
Dim ctl As Control
For Each ctl In Me.Controls
If Me.TimeRequired.Value = "-1" Then If Me.Tag = "Fermyesno" Then
I am trying to populate a record in a table "M_Paint" using an unbound form. I cannot get it to work. I am posting the code below for reference. I can post the database if any one will like to see. The complicated part is that I am trying to generate a text value for one of the fields in the same record based on some of the selections made in combo boxes in the same form... (it does sound complicated, doesnt it!) Please feel free to ask questions; Thanks a ton! Quote: Private Sub cmd_ip_catcode_Click()
'Error Handling On Error GoTo cmd_ip_catcode_Click_Err
'Declare Variables Dim db As DAO.database Dim qdf As DAO.QueryDef Dim DAOrs As DAO.Recordset 'Variable to collect selections from list boxes Dim strcataloguecode As String Dim strnumber As String Dim srtcolor As String Dim strbasemetal As String Dim sSQL As String
' Identify the database and assign it to the variable Set db = CurrentDb t = "M_Paint" Set DAOrs = db.OpenRecordset(t) With DAOrs .AddNew .Fields("Catalogue_Code") = strcataloguecode .Fields("Base_Metal") = Me.cmb_ip_basemetal .Fields("Paint_Type") = Me.cmb_ip_painttype .Fields("Color_Family") = Me.cmb_ip_colorfamily .Fields("Metallic") = Me.cbx_ip_metallic .Fields("Surface_Quality") = Me.cmb_ip_surfacequality .Fields("Number_of_Coats") = Me.txb_ip_numberofcoats .Fields("Supplier") = Me.txb_ip_supplier .Fields("Product_Name") = Me.txb_ip_productname .Fields("Color_Name") = Me.txb_ip_colorname .Fields("Color_Number") = Me.txb_ip_colornumber .Fields("Top_Coat") = Me.txb_ip_topcoat .Fields("Pre_Finish_I") = Me.txb_ip_prefinish1 .Fields("Pre_Finish_II") = Me.txb_ip_prefinish2 .Fields("Finish_Comments") = Me.txb_ip_finishcomments .Fields("Size") = Me.txb_ip_size .Fields("Number_of_Samples") = Me.txb_ip_numberofsamples .Fields("Compilation") = Me.cbx_ip_compilation .Fields("Location") = Me.txb_ip_location .Fields("Date_Received") = Me.txb_ip_datereceived .Update End With
' Getting the initials from base metal table strbasemetal = "SELECT L_Base_Metal.Paint_Code FROM L_Base_Metal" & _ "WHERE (L_Base_Metal.Base_Metal) LIKE [Forms]![F_Input_Paint]![cmb_ip_basemetal];" Debug.Print
' Getting the numbers from record Number strnumber = DAOrs("Record_Number")
' Getting the color number strcolor = "SELECT LP_Color_Family.Paint_Code From LP_Color_Family" & _ " Where (LP_Color_Family.Paint_Color) LIKE [Forms]![F_Input_Paint]![cmb_ip_colorfamily];" Debug.Print
' Inserting the Catalogue Code into the table strcataloguecode = " & strbaasemetal & '-' & strcolor & '-' & strnumber & "
I'm in the Military, and I have a form that shows everyone's personal and training information, and also shows if they're deployed to another part of the world. If they are deployed, it will be shown in a drop down box... I have another drop down box that needs to show where they are deployed to. Here's the problem, I need for this one to be self populating so that if the location is not currently listed, then it can be added. Here's where I'm at so far: I have a table named "Location" and it has the fields "Location Name" and "Auto number". My field where I want this to be displayed is named "Where?" (as if asking the question, Where is this person deployed?) This is the code I'm using:
SELECT [Location].[Location Name] FROM Location AS [Where?] ORDER BY [Location Name]
It keeps giving me the error message: The SELECT statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect.
Could someone please tell me what I'm doing wrong?
I need to query information from a table. I need to work with 2 fields. One field is Customer Number the other Field is Notes. Each time a note was entered a new record was created. I want to list the customer and all the notes. I will then use this in another query and report. Is there any way to convert the many records to a record for each customer with many notes. I hope this makes sense. Jeri
Right, i have access 97 on my pc, yeah i know its old skool and ishould really upgrade, but thats expensive, and im only a student.
Basically i wanna create a stockfile/invoicing/sales log/purchase log for my business, but im getting completely lost. i figured best way to start out in the database was to get a stock file sorted out. but thats proving to be problematic before i even start!
what i would like to do is have several categories and then further sub categories and sub sub categories.
____drink >>>>____soft >>>>>>>>____water >>>>>>>>____pure fruit juice >>>>>>>>____squash >>>>>>>>____fizzy/soda >>>>____alcohol >>>>>>>>____beers >>>>>>>>____wines >>>>>>>>____spirits
but when entering data , say im entering product details of a beef spare ribs, if i select "food" in one column i want it to only allow me to select the subcategories within "food" (i.e meat, bakery or dairy) and then once ive selected that i then select what type of meat, as opposed to having a whole list of dairy, bakery, meat, soft and acohol all coming up. and same with the sub-subcategory.
cheers for all your help, if u wanna chat further you should find me on msn!
And another table country: id that has the values: 1,4,5
Basically is there a query that can be written for me to check if ALL the countryids from the country table will return an artist, and if not can it let me know. For example obviously there is no countryid with 1 in the video table so Im not interested in that batch in the country table. I know I can just execute the query 3 times to see if a result is produced but what if the country table is massive (btw the country table is generated dynamically using xml). Any help on this would be very mch appreciated
I am very new at doing Access database. I have read stuff on it but I just keep confusing myself. I am trying to build a master database that holds all information on our teachers. Such as staff development workshops attended, keys that have been issued to them and so on.
I have three tables. One has the teachers information such as name, ss#, address etc... The other table will have the staff development title and dates and the third table will be a key database.
I want to be able to open the database in a switchboard, which I have done before and be able to add information in a particular teacher's file such as keys issued or returned, staff workshop just attended.
I have attached a sample of what I have started. Could you lead me in the right direction? Thank you.
I have a database with a table containing drawings recieved. Each drawing can have many actions, first action is sender detail, date, and letter ref. Second action is sender detail, date, letter ref and status (ie acceptable / not acceptable) I can run a query which looks for all drawings with a status of not acceptable but i then lose info on sender letter ref or I can run a query which gives the senders letter details but for all drawings, not jut those not acceptable. What I want is a query which looks at all docs sent by C1 AND not acceptable.
Here's a challenge... i thing that there's a very silly method to prevent this from happening but unfortunately i can't figure out how... there's the challenge...
we have a car that makes a distance from 01/01/2007 and returns 30/01/2007 and the same car makes another distance from 12/01/2007 and returns 15/01/2007 that's not gonna happen if the prog checks the dates... but till now was checking the 1st date (for ex. the from or the to) so if the car leaves and returns during another wayout the drivers gets more money and the truck legaly should be in a certain distance this time and not in a second one... PLEASE HELP ME!!!! MY BOSS IS LOOSING MONEY AND I'M THE ONE THAT I HAVE TO FIND THEM!!!! :eek: :eek: :eek: :confused: :) :)
