 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / December 2003Setting up continuous fields
I am trying to figure out how to set up continous fields in a new database that I am creating. What I mean is (example of what I need, not the information I am inputting) Field 1 = Owner Field 2 = Residence Field 3 = Vehicle 1's Date of Purchase Vehicle 2's Date of Purchase Vehicle 3's Date of Purchase Vehicle 4's Date of Purchase Vehicle 5's Date of Purchase Field 4 = Vehicle 1 Vehicle 2 Vehicle 3 Vehicle 4 Vehicle 5 Field 5 = Vehicle 1's HP Vehicle 2's HP Vehicle 3's HP Vehicle 4's HP Vehicle 5's HP Field 6 = Vehicle 1's Color Vehicle 2's Color Vehicle 3's Color Vehicle 4's Color Vehicle 5's Color As you can see, I want to have continuous cells in field's 3-6. They are all in relationship to Field 1. But I don't want to have numerous fields as some of Field's 3-6 would be different. One owner may have 3 vehicles whereas another owner may have 20. How in the world do I make Access do this dirty work? Thanks in advance! Sarr Hi, You make two tables. The first one is made of fields 1 and 2 only (and what one owner owns once). Call the table owners. The second table is made of fields 1, 3, 4, 5, .... and what belong to each vehicle. Consider that a given vehicle "owns" one "owner". Call the table vehicles. For one owner have as many ROWS in vehicles as required. That's all. Hoping it may help, Vanderghast, Access MVP > I am trying to figure out how to set up continous fields > in a new database that I am creating. What I mean is [quoted text clipped - 34 lines] > Thanks in advance! > Sarr Michel, Thanks for your response. I think I confused myself, and not enough detail on that example, so let me outline it better with the actual data. The basic information I'm trying to draw out are: Field 1: Claim # (PK) Field 2: Monitoring Well # Field 3: Date of Sampling Field 4: Benzene Results Field 5: MTBE Results Field 6: BTEX Results Field 7: TPH Results There will be multiple information for fields 2 - 7. Example of what a report should look like after information is entered into Access: Claim 40050 Monitoring Well #1 Date of Sampling: 01/01/01 Benzene: 100.3 MTBE: 80.6 BTEX: 1135.7 TPH: 456.3 Date of Sampling: 03/01/01 Benzene: 99.3 MTBE: 77.6 BTEX: 1102.7 TPH: 568.3 Date of Sampling: 07/15/01 Benzene: 156.3 MTBE: 64.6 BTEX: 999.7 TPH: 357.3 Monitoring Well #2 etc. Monitoring Well #3 etc. Each claim would display a different number of monitoring wells. For each well, there will be different number of dates where samples were taken. As you can assume - it may dramatically vary on number of wells/dates. We have claims that have up to 30 monitoring wells that were sampled monthly for a period of 3 years. Other claims may have under 5, or 5-10, etc, monitoring wells that were only sampled quarterly for a period of 2 years (many variables). All this information needs to be entered. I need to create a form for someone to enter the information. Afterwards, I will need to run a report that looks similiar to the above, along with graphs after calculations are made. I've been playing with this for the past week. Field 1 is already in a table with other basic information, along with other tables. The problem is setting up another table (or form?) that would allow multiple information in the same field without actually manually going through and entering numerous rows/columns? for this. Make any/more sense? Thanks, Sarr >-----Original Message----- >Hi, [quoted text clipped - 53 lines] > >. Hi, A possible design would be Samplings SampleNumber, Well, DateTaken Benzene, MTBE, BTEX, TPH ' fields 1 1 xxxxxx 100.3 80.6 1135.7 456.3 ... ' data It is then a matter to link the samplings, to the claim Claims ClaimNumber, SampleNumber 1 1 1 2 1 3 ... In a form, you can display the whole thing with a main-form (Claims) sub-form ( Samplings) design. If you think other measurements can be added in the future, may be you could remove the Benzene, MTBE, BTEX, .... fields from Samplings, and make another table, measurements: Measurements SampleNumber, Product, Measurement 1 Benzene 100.3 1 MTBE 80.6 1 BTEX 1135.7 ... and, since the field name is independent of the object of the measurement, you can add Product= whatever, if you ever measure it! On the other hand, it give you another level of form-subform-subsubform, which may soon appear to be too complex... Hoping it may help, Vanderghast, Access MVP > Michel, > [quoted text clipped - 131 lines] > > > >. Thanks again. I will take a look at this route and see what I can come up with. Your input helps. Thanks! Sarr >-----Original Message----- >Hi, [quoted text clipped - 175 lines] > >. > How in the world do I make Access do this dirty work? Assuming that a vehicle only has one owner, then you need one table for Owners and one table for Vehicles. If owners can share the same vehicle, then you need another table for Ownership. For a working example of a one-to-many relationship, see any book on database design, or the help files, or look round the Northwind sample. Best wishes Tim F Tim, Printed out the information - and also gonna view Michel's suggestion. Hopefully now I can finally get this thing to work. Thanks! Sarr >-----Original Message----- > [quoted text clipped - 12 lines] > >. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.