Am new to the forum and it seems a good place for help,
Am having some big problems with a database that i am using for a company. The problem being that users need to enter ref numbers for each record on to the database called "c3" numbers, there could be a unlimited amount of these numbers(they do need to be able to reported on though) per record
At the moment i have a serperate form linked to my original form with a lot of seperate fields labelled like "c3number", "c3number1", c3number2" and so on which the user enters the c3 numbers into ans saves
however it is becoming very hard to do counting reports with it set up like this.
has anybody any ideas's on how i could make the setup simpler?
Hoihoi, Well, Hello..Never thought I'd register on an Access forum, but, hey, why not!I'm new to Access, never really liked it..but this assignment has changed me.. i really like Access and well DBA'ing in general!I need a bit of help with a few things:-----I hate registering on sites JUST asking for answers >< I hope to learn a lot of stuff from here!-------Just like to add it's for an Assignment at college, i KNOW I KNOW you people can't help with assignment work, BUT it's not like i'm asking you to do it for me, just need a bit of help Basically,I've got a database -It's based on a Mobile Library which visits nurseries.The nursery can loan a book, if a book is currently being loaned by a nursery and another nursery wants to loan that book, they can reserve it untill it is returned.Basically, I've got a few forms:21233I can do everything fine!Everything seems to work, i can loan and reserve..i can add books, nurseries and authors..HOWEVERwhen it comes to deleting.. it's a different story!On my delete_author form, I've got a Combobox which loads the authors and then i've got a Delete button which should delete the author which is in the combobox---- I've google'd for ages..and can't make much sense of any of the replies people have had..Is there anyone who can help me with this?Delete the author which is in the combo box..I can supply any details if people are willing to help :oWould LOVE to hear a reply from anyone!Cheers :)
I have a form that I created using autoform from my main table. The form is just for the user to enter a new record. But when opening the form, how do I get the fields to be blank and on a new record by default, rather than starting on the first record with the fields already populated?
And then if I make a button that says "add" but just closes the window, the data should automatically save right?
I am fairly new to access so please excuse my lack of knowledge. I am using Access 97
What I am trying to do is create a system where I ask for a zip code, then disply the closest 5 results to that zip code.
I currently have every single city with ZIP code in Michigan. I also have the list of results with their zip codes.
Since you cant relay on ZIP codes going in order, I also have the Latitude and Longitudes for each ZIP code. Of course I have the lat and long for my possible results.
How can I get the 5 clostest results based on two different factors (Lat & Long)? Would I want to construct a Query to do this?
Any tips or sites you can refer me to would be great! Thank you!
Ok, I have studied up on relationships, and read a lot of these threads but, I still have yet to find help. It seems a lot of the people on here have difficult problems that require dificult answers. Basicaly I have two tables Tblresort and Tbllifts, now there can be many chair lifts for each ski resort, so this would call for a many-to-one relation ship. Now, I create a relationship for the fields, and enforce refrencial integrity and all that. Now what do I put into the related field in Tblresort to get the lifts with ID#'s 1-8 to be related to the first resort.
I hope this all makes sense, so far I can only get one lift conected to the resort.
I have to create a new table for some data I have from work. The data was imported within quotations though, so it won't let me copy and paste it untill the quotes are removed. I have over 3000 data values in each column, so I can't just go through and delete every quotation mark. Is there any way for me to select a column and remove the quotations around every value?
I am brand new to Access/VB as of Thursday. I am an aspiring programmer still in college. My proficiencies are more to the c++/HTML side of things right now, but I am in dire need of some assistance. I was recently given a series of projects to determine if I am qualified for a particular job. I have completed them all but one. I am modifying an invoice form in Access. I have accomplished all but two of the tasks on this last project. I need to modify the invoice form so that whenever a payment amount is entered, the payment date box I created populates with that date. I know I'm really close to it, but I keep getting errors and the only thing populating the box is #Name?. I also need to open up the print/preview mode of the invoice report when I click the print button as opposed to the current form it opens up to print from right now. If anyone could offer any assistance/advice on anything...even a tutorial that might point me in the right direction that would be excellent. I have until Tuesday to figure this out =) Thanks a bunch in advance!
P.S. This is where I thought I needed to execute the update for the payment date since it is where the payment is entered. If this doesn't help at all then I apologize.
Private Sub I_Payment_Exit(Cancel As Integer) Me.I_Tax = NullToZero(Me.I_Subtotal * Me.I_TaxPerc) Me.I_Total = Me.I_Tax + NullToZero(Me.I_Subtotal) + NullToZero(Me.I_ShipChg) If Me.I_PaymentLock = True Then Else ' Me.I_Payment = Me.I_Total End If Me.I_Balance = Me.I_Total - NullToZero(Me.I_Payment) End Sub
Sorry to add to the email threads, already posted on this forum. I have already searched on the forum for the relevant help but im still struggling. As am a newb when it comes to this sort of thing, and everythink is an on going learning curve.
Background, i work for a architectural firm, my boss would like the database to send out automated emails, to the local planning authority.
I have a query called "ApplicationEmailCouncilQuery" in this i have the following fields. ApplicationProjectID, ApplicationSubmitted, ApplicationRef, ApplicationDecisiondate, ApplicationContact, PlannersEmail, ApplicationEmployee, Email.
How do i get access 2k to automatically send out emails to each of the results individually as produced fromt he query, every 2 weeks.
I want the email to use the fields from the query. To: "PlannersEmail" Send: Your Application ref is "ApplicationRef" submitted on "ApplicationSubmitted" Message: To ApplicationContact, Please could you give me an update on the application "ApplicationRef" which we submitted on "ApplicationSubmitted".
Please reply to this following address "Email"
Yours Truly
"ApplicationEmployee"
-----------------------------------------------
Sorry for the long post, any help on the above matter would be greatly appreciated.
ok im working on a database that a company sends its employee to school and tracks there training.what classes they take what not.. my tables were fine until i realized that supervisors were also employees so i need to add a supervisorID i dont know how to do it without screwing up all my foreign keys i assume it goes into my employee table and department table but then where do i put my departmentID foreign key?
I currently have a database which I believe is in 3NF. It's completed as such, but I have recently come accross normalization methods. Because of this I would like to know how my 3NF tables could look in 0NF, 1NF and 2NF. For both the purpose of ensuring the database is correct now, and for future reference...to do a more professional job next time!!Here's how the tables currently look:CUSTOMERS(CustID, CustName, CustAddress)ORDERS(OrderID, CustID, OrderDate)ORDERDETAILS(OrderID, BookID, BookPrice, Quantity)BOOKS(BookID, AuthorID, BookName, BookUnitPrice)AUTHORS(AuthorID, AuthName, AuthAddress)It's just basically a simple book royalty system. I have looked around various sources for examples, but my database has a few more tables in 3NF than most examples show.Thanks guys.
For my job history entity should I just store my placement number and remove the candidate number? Since placement number already determines the candidate number in the placement table.:confused:
I have decided to normalize further and now have brain freeze. I have a table 'tblComputers'. I have removed Computer Make + Computer Model to own tables and linked via ComputerMakeID & ComputerModelID, this is working OK.
I then decided to remove RAM to its own table. I created tblRAM. I added additional rows in tblComputers 'Memory Slot 1' Memory Slot 2' etc. tblRAM has 2 columns RamID & RAM so....RAMID 0 = 0 RAMID 1=128 RAMID2=256 etc..
My issues? What to link RAM to relationship wise then allow users to select multiple instances for different memory configurations to save values back to 'Memory Slot 1' 'Memory Slot 2' in tblComputers?
The first table contains Employee ID, and Name of Employee
Second table contains Employee ID, and an autonumber
The third table contains an autonumber and a unique PIN
The 1st/2nd table are connected by Employee ID, the 2nd/3rd table connected by the autonumber. Now not every Employee gets the unique pin and the 3rd table gets re-done every year. This structure works great for all "current" employees. Employees that leave get deleted out of the 1st table, thus causing them to get deleted out of the second table due to relationship. This becomes a problem because I need to keep a record of who had what unique PIN, regardless of who is still assigned one or not. Anybody have any ideas on how to do this.
I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions.
I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on.
My basic set up for now is this: Table One: contract information Table Two: Payment information Link Key: Serial Number from contracts These are my questions:
1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table? 2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense). 3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work.
Sorry if this is so confused...I'm sort of learning as I go along.
I am having some questions on a order entry DB I'm working on, (modifying an existing one). I've attached a shot of the current relationships to demonstrate. Basically to start this was an order entry db to place orders to one vendor, so this was not a problem then... But.. Now I am modifying it to order from more then one vendor. So, I have the order form. You first pick the Vendor the order is going to, then the Customer and delivery site. Next you move to a subform to pick your items from a dropdown. So the problem... I have a new table for the Vendors. This table is related to the "Order Table" on a one to many. But what I also need is the VendorID to be in the table "tblItem" Thus when the Vendor is picked in the main form ,it can be filtered in the subform to only show items from this vendor. Now this would work by placing the Vendor ID in the "tblItems" table and relating the two. But would this be proper normalization to have the VenforID as a FK in two tables???
I am putting 80 rows (personnel) of data with about 20 columns (training courses), to track training for my people. Is this enough that I would need to make several tables, or am I correct in thinking I can put it all in one table? From what I have been reading, this is a pretty small table, but I want to make sure.
Hello all, I have been tasked to create a database to track tooling information for my company. I have a solid idea on the process involved and would like some help getting my tables and relationships setup with normalization. I have noticed in the short time I have been learning access that normalization is the key to happiness.
A short synopsis on how this should work: A model is built from many different parts, these parts are sometimes (usually not always) associated with the tool that is used to build them. A tool could be used to make several different parts and some parts use several tools. In addition to this, a model can use a different quantity of parts to build it and those parts can require different quantities of tools to produce them.
I have tried several different ways to relate these tables together, and would like to ask the braintrust that is this forums for any help they could give me.
I have a contract-tracking database that has been in use for about a year. It used to be that every contract had the same number of periodical "applications" with it - the companies we worked with would send in 4 applications over time.
So, I had this set up:
tblCompanies (the companies with whom we work) - CompanyID (Primary Key autonumber) - CompanyName - Address, contact info, etc.
tblContracts (the contracts we issue) - ContractID (Primary Key autonumber) - CompanyID (foreign Key) - DateIssued
tblApplications (things companies send back to us over time) - AppID (Primary Key autonumber) - AppDesc (description of the application - something like "Application 1", "Application 2" , etc.
tblAppsRecvd (Join table/log of apps received) - ContractID (foreign key to tblContracts, so we know which contract this applies to) (also a composite primary key, so we don't duplicate an app to a contract) - AppID (foreign key to tblApplications, so we know which application we rcvd) (also a composite primary key, so we don't duplicate an app to a contract) - DateRcvd
Pretty self explanatory. Everything was keen, and I was reaping the rewards of a job well done.
But now we've expanded out contract offerings. He have introduced 2 new types of contracts, and each contract has a different amount of applications associated with it.
Contract 1 has 4 apps. Contract 2 has 2 apps. Contract 3 can have 1 to 8 apps. (The last app before a contract closure is always considered "Final" - so if we get 5 apps, it would be apps 1, 2, 3, 4, and Final.)
I've tried implementing a couple different schemes, but none seem to be fitting the bill. The type of contract will dictate how many apps we receive - i.e. If we are using Contract 1, then we KNOW we'll recieve 4 apps over time. So, is this as simple as adding a "ContractTypeID" to the tblContracts with an appropriate tblContractType table, and expanding tblApplication to more apps, or is there a smarter scheme?
Im setting up a database that contains information about printed circuit boards that are being developed for our current project. The information that i need includes the following:
Board # - 3 digit number Board Name - Module # - 9-digit number Module Name - Project Leader - engineer responsible for design In/Out/Oem - inhouse, outsourced, or purchased OEM Designer - engineer or outsource contractor who designed the board MFG/Assembler - company responsible for building the boards Directory - file on network drive the drawings are located in
There can be mutiple boards in a single module. So i have 3 tables right now.
pcb_tbl - this includes all the info above minus the module info.
Module_tbl - this table includes the module number and name and responsible engineer, with the module number being the key.
module_pcb_tbl - this table lists the module number in the first field linked to the module table, and the pcb number in the second field linked to the PCB table.
I have a PO DB that I'm trying to normalize. For the most part it is normalized but there are 2 fields that are not normalized because they repeat data. So I'm trying to restructure a bit and having some problems when it comes to data entry via forms.
There is also a PO table above this one but is not used in the subform for the details. My old set up for my PODetailsTable
PODetailsID pk PO fk Item Quantity Price Allocation
I want my new set up to be PODetailsTable
PODetailsID pk PO ItemID fk Quantity Price AllocationID fk
ItemTable ItemID pk Item
AllocationTable AllocationID pk Allocation
On my form I can't enter anything that is already on the ItemTable. I looked at some microsoft sample DB's and they have you enter the item from a different form then you can select it in from the combo box which would really slow down data entry peticularly early on.
Is there a way I can type the Item in and if its new, it will be put it on the ItemTable associate an ItemID to both tables?? But if its already in the Itemtable just apply the already existing PK on ItemTable to the FK ItemID on the PODetails Table for that record??
Here is a A2K format of it which is how it currently works. In the relationships window you can see an example of how I want it but can't get to work.
I have recently been blessed with an 80 column excel sheet and am in the process of producing a normalized db; however, I am not sure that I am following the rules correctly. If anyone is willing to check it over, below is a copy of my potential relationships.
Also, I am willing to learn. So feel free to harass and advise.
Many thanks in advance. ---------------------------
Name(StudentID, First, Last, Middle, Maiden, Spouse, Prefix, Suffix)
I'm designing a database for my place of work. I've created the tables and relationships, and would like to know if the design conforms to Normalization?
A .jpg is attached to this message showing the relationships.
I have two forms bound to different tables. I will use an example to illustrate my problem. I have 4 people who have each sent me 5 documents. I do not currently know the identity of these 4 people so I can not create a lookup table. In the future they will continue to send me docuements. In addition, other people, currently unknown, will send me documents. My first form records the author and the second form records the document info. I have 3 problems. The first is setting up a procedure to make sure the authors name does not already exist. The second is creating a link between the forms so that the authors name is linked to the document the first time. (If you have read any of my other posts you know this is part of law office db and everything is already linked by client and case so this would be a third link for these two forms. All 3 links are necessary here because the same authors may send docuements on more than one case). And my third problem is setting up some sort of query or lookup procedure so that I can link the author to the docuement. I cant use the author as a lookup table because I dont know the authors ahead of time and there will constantly be additions to the authors. Its complicated bythe fact that I need to check each docuement to see if its written by an existing author or new author. I'm almost tempted to combine the tables and use one form but that would be serious violation of normalization. Some Authors will be sending in excess of 50 documents. Can anyone help me. I am completly at a loss as to how to do this.
I am building a database for a clinic to house patient information. At the moment, there are 300-400 fields in 6 tables all linked in one-to-one relationships by a file number (PK). The clinic wanted the forms to look like the paper forms they use to assess patients (15 different forms), so I created forms that are all linked by command buttons so they open sequentially, and the File Number autopopulates in the next form. Trouble is some forms are based on multiple tables, and I keep getting the message..."causes duplicate values in primary key...cannot save record at this time". I would like to add a new record to all the tables by entering the PK in one field first, and then enter the data by opening the forms in edit mode- this seems impossible. I have this sinking feeling that my tables are not normalized and my design is flawed. This is the type of information I have, and would like to keep confidential data in a separate table with limited permissions:
tblConfidentialDemographicInfo file number patient name dob parent name mailing address city postal code telephone number parent employer pediatrican child psychologist school name teacher name custody arrangement research study participation (10 studies...option group, 1 for yes, 2 for no)
tblDiagnostic (MH disorders, all option groups (1 for yes, 2 for no) Tourette's ADHD Depression Bipolar Learning Disability Etc.
tblHomeInformation many questions with multiple responses in drop-down box num children in home pregnancy details (bleeding, c-section, breech- again all option groups) numerical scores on questionnaires
tblSchoolInformation test scores observations in school split grade special help (list)
tblPsychoeducational Assessment IQ scores for subscales and full scale scores on achievement tests (numerical)
Any suggestions appreciated, Thank you very much in advance.
