|
blojo02184
Big Red



Registered: 05/15/13
Posts: 3,527
Loc: Maine
Last seen: 3 months, 4 days
|
microsoft access database help
#20961923 - 12/11/14 12:47 PM (9 years, 5 months ago) |
|
|
so this is my final project... for database management. (should have been called database building or something along those lines)
But my problem lies in a form im trying to make, which might have been easier to split into two forms, but whatever.
My form contains 2 drop down selector boxes. Number 1 combo box chooses an ID from one table and pulls up information regarding that ID. The second drop down box needs to select a certain record pertaining to that ID.
The second ID has 2 tables linked to it, for recording other data with it. I have posted the database in the gourmet section because I didn't know this was here, I only knew of the shroomery university.The database
its the data entry form im working on... this is due next Monday... so if you have words of wisdom please share them soon.
If you don't share before Monday that's fine, because this project is going to be given to the shroomery community to help individuals identify profitable genetic strains that they have isolated, using various means of growing individual cultures.
So don't do this for me, do this for us 
Happy Festivus also
|
koraks
Registered: 06/02/03
Posts: 26,726
|
Re: microsoft access database help [Re: blojo02184]
#20962644 - 12/11/14 03:56 PM (9 years, 5 months ago) |
|
|
I'd make a query that fetches all relevant data for the IDs you're looking up, from all the relevant tables. Then make one combo box with a select from the query with all the data. You can then pass those data to the update query you're probably using. Note that I haven't opened your actual database, so maybe this won't work in your specific situation, but in cases like this, I'd go about it like I described.
|
blojo02184
Big Red



Registered: 05/15/13
Posts: 3,527
Loc: Maine
Last seen: 3 months, 4 days
|
Re: microsoft access database help [Re: koraks]
#20962726 - 12/11/14 04:11 PM (9 years, 5 months ago) |
|
|
kinda... but no 
because I'm pulling from 4 tables (a,b,c,d) table a(1) goes to table b(many) table b(1) goes to tables c&d (many) tables c&d are the end tables in the relationship.
my teacher said it might not be doable. if I pull all tables and necessary fields into a query, it won't pull any data because there is too many values for one field.
what the work around my teacher suggested was to make a tab on the bottom of the form, to check your in the right spot, rather than use it to guide your selection.
it's a control source, record source problem I'd like to work around still
I understand if you can't open it, ms access is not a very common program.
|
koraks
Registered: 06/02/03
Posts: 26,726
|
Re: microsoft access database help [Re: blojo02184]
#20965031 - 12/12/14 02:20 AM (9 years, 5 months ago) |
|
|
Oh, I have it on my laptop, but I'm posting from my phone 
If there is no I'd on which to make a join between a,b and c,d, my solution won't work (it will return the Cartesian product, ie an unwieldy list). A separate combo box should work though; why wouldn't it?
|
koraks
Registered: 06/02/03
Posts: 26,726
|
Re: microsoft access database help [Re: koraks]
#20965091 - 12/12/14 03:18 AM (9 years, 5 months ago) |
|
|
Ok, I just had a look at your database and I can't say it's entirely self explanatory, so I'm not sure exactly what you're trying to achieve.
It looks like you're recording data on a specific grow (with some properties associated with it, such as the species which you're growing and the optimal conditions), with the ability to log the humidity (and perhaps other parameters) at several moments for each grow; is that correct?
In any case, it seems like you're making something relatively simple into something pretty complicated.
Why not have three data entry forms for different growth 'properties' (separately for humidity, pin, flush). The growID is your main entry point for each form and on each form, you can display the associated data from the culture and genus tables (can easily be fetched with a query, since these data are linked through CultureID and GenusID). Then you can enter data with either a single record form (pins, flush) or a table form (humidity) on each data entry form.
Combining data entry for all three end tables (humidity, pins, flush) is doable, but will require some unnecessarily complex programming, as you'd have to check on the submission of the form which data have been entered and output has to be generated to the associated tables. Three separate entry forms would be much less complex, and for user friendliness you could include buttons to easily navigate between these three forms (passing GrowID as a key to get the right data on the target form).
TL;DR: combining all three inputs on one form is unnecessarily complicated. Split it into three forms and the exercise becomes straightforward.
|
blojo02184
Big Red



Registered: 05/15/13
Posts: 3,527
Loc: Maine
Last seen: 3 months, 4 days
|
Re: microsoft access database help [Re: koraks]
#20965331 - 12/12/14 06:04 AM (9 years, 5 months ago) |
|
|
yes, sort of. I want to be able to put a label on an isolate and track data points of different grows using the same isolation, trying to achieve the "sweet spot" for that culture, since everyone culture is different. even within the same species.
yeah, that's probably what I'm going to do after the project is over.
if I made a combo box on say the home page form, what would the macro be to pull up a form to enter into that 'record? a simple openform macro?
or when you open the data entry form, have the control source be the humidity table where I'm entering data, but the growid combo box to choose the record I'm adding too?
you are right though, overcomplicated. but just because of this stupid project.
I'm going to rework it after to make it shareable, and more understandable.
I have removed the temp field from the growth table and threw it on the humidity table.
|
blojo02184
Big Red



Registered: 05/15/13
Posts: 3,527
Loc: Maine
Last seen: 3 months, 4 days
|
Re: microsoft access database help [Re: blojo02184]
#20978443 - 12/15/14 08:10 AM (9 years, 5 months ago) |
|
|
Log in to view attachment
so this is what the final project looks like....
Definitely re working this over winter break to be a little more user friendly...
|
koraks
Registered: 06/02/03
Posts: 26,726
|
Re: microsoft access database help [Re: blojo02184]
#20978997 - 12/15/14 10:53 AM (9 years, 5 months ago) |
|
|
Looks like there are some bugs to be ironed out still. When I try to enter a new grow, it won't let me select a genus since it is an autonumber field. Apparently there's a bug in the culture entry form and it assumes that it should refer to the cultures table, while the field should refer to the grows table. Plus, 'pleurotas' (which is a typo; should be pleurotus) occurs twice, while I'd say that the genus name should really be unique. Also, in the temperature/humidity form, it's kind of odd that humidity is selected from a drop down box, while temperature is free data entry (but with a weird entry rule). So some stuff to be ironed out still, but overall, you're getting somewhere!
|
blojo02184
Big Red



Registered: 05/15/13
Posts: 3,527
Loc: Maine
Last seen: 3 months, 4 days
|
Re: microsoft access database help [Re: koraks]
#20979377 - 12/15/14 12:27 PM (9 years, 5 months ago) |
|
|
yeah, im going to definitely simplify it hard!!!
|
|