Lookup Values

[inq80]

Lookup Values:SQL or dlookup?

Hello Everyone,

I'm new and wondering if i could get some advice?

You'll have to bare with me though as I'm new to Access and don't know all the lingo yet... I've only been playing on it a month and only a chapter into my training manual.

The issue I've got is that I'm learning about databases and how useful they are. I've always been an excel person and done everything in that, until recently when It was pointed out to me that a database would be far more useful for my work given it's size. So...

I've set up a database with multiple tables and defined relationships (love that! - a much better version of defined lists in excel and then some), and the table I'm creating a form from is a result table.

In this table is the student, the month, the year, the measure and the result. So for example "Student A" result for "Measure 1" would appear under Jan, Feb, Mar etc. Jan might be "A", whilst Mar might be "C"

There's sixteen measures in all and I would like the form to auto-populate with the results, once the student, year and month have been selected. For ease of purpose, the form defaults on the year and month to that at the time of the form being opened, which just leaves the student to be selected.

As ever, because I'm not up on the lingo, I cannot find useful tutorials online as it appears I'm searching the wrong terms, so was wondering if anyone here could point me in the right direction on how i could do this?

Basically it's an Access version of INDEX/MATCH from excel I'm looking for. If "Student A" is selected, and 2015 + Nov are the default values, then Nov's result would appear in Measure 1's textbox; and if Mar is selected, then Mar's result would appear etc.

Any help or pointers would be appreciated.

Thanks

 

[sneuberg]

In this table is the student, the month, the year, the measure and the result.

I would guess this has some parent table and "the student" is a foreign key? It might help us understand what you are trying to do if you unloaded a screen shot of your relationships

 

[inq80]

I would guess this has some parent table and "the student" is a foreign key? It might help us understand what you are trying to do if you unloaded a screen shot of your relationships

Hi Guys,

Thanks for the replies; my internet has been down so apologizes for not getting back to you sooner.

Here's the relationship table attached.

From what I seen, I understand it's best practice to avoid repeating information in a table? I 'm unsure how best to layout this database other than i've already done so would this be a problem?

 

[inq80]

The form has three combo boxes; two auto populate with the current month and year. The third is for the user to select the staff member. The rest of the form has 16 text boxes with labels specifying which measure each text box relates to.

Ideally I want the text boxes to show the score achieved once the user defines which staff member, month and year they want to see, which means I need to know how to get the text boxes to show the score which corresponds with the measure, month, year and staff member.

So if staff A, Nov and 2015 is selected the result achieved that month and year by that staff member is displayed in each of the measure score boxes. If the month is changed to Feb, then each of the scores change to that corresponding month's score.

 

[sneuberg]

What's a measure and what dictates there are 16 of them? If measures are like tests, then I suggest making a table for them so you don't have an arbitrary limit of 16. You shouldn't need 16 text boxes either.

 

[inq80]

What's a measure and what dictates there are 16 of them? If measures are like tests, then I suggest making a table for them so you don't have an arbitrary limit of 16. You shouldn't need 16 text boxes either.

The measures are a bit of a red herring, as I do not their number being relevant. At the moment theirs sixteen of them. Next year it could be 13 or 21. They're just key indicators of performance.

At the moment they're in the results table with a separate measures table just to ensure no measure is added to the results table which does not match an approved one in the measures table.

I suppose I could have a separate table for each measure as 16 tables would be easier than a separate table for each staff member that could go into the hundreds. Or I could do each month which would be less that 16...

 

[sneuberg]

I suggested making a table for the measurements, not tables for them. The fields might be Measurement ID, Measurement Name, etc.

 

[inq80]

Sorry sneuberg, I don't see how that would be different to what I have?

The measure names are the key ID in the measure table. I understand that it's not good practice to have tables that require repeat entries which is currently the case with the month, staff and measure being repeatedly used, so could reduce this to having individual month or measure tables. This doesn't help though with my original question...

 

[inq80]

Have a look at this Post:-

Thanks Uncle Gizmo - bit too above me at the moment though...

 

[inq80]

I've had another play and set up a practice file which I've attached. This should hopefully show more clearly what I'm after;

I need the user to be able to select the Staff, Month and year in the form and then have the form display those particular results when the search button is clicked.

Can anyone suggest how best to do this? Is this something that can be done using SQL or a dlookup function?

Thanks

 

[inq80]

Is it a filter I should be looking at; Anyone?...

 

[inq80]

Are you saying:- table "Measures" is already full of results, and you want table "Results" field "MeasureID" to be filled automatically?

Hi Gizmo, yes that is what I'm saying. Sorry I wasn't clear. The "results" table will already be completed and by finding the values, all the user will be doing is updating them. So I just need the form to display the values searched for in the staff, month and year comboboxes...

 

[inq80]

Sorry, I didn't realise I wasn't being clear with my requests.

Your reply Gizmo seems to be based on the original relationship table I attached. I've change it seen then hence the attachment of a sample table later on in the thread.

As for the difficultly in interpreting my responses; I did say at the beginning I new at this and new to the lingo so please be patience. I've only been on Access for 10 days now.

I don't how else to explain it?

Essentially the results table will already be filled with scores for the individual Measure fields. The user would use the form to select a staff member, month and year from the combo boxes. This would indicate which PeriodID they are looking for from the PeriodChecker table. That same period ID will be used in the results table and once the PeriodID is selected, I need the textboxes to display the score from that period.

So if staff 1 is selected, Jan selected and 2015 selected, the PeriodID searched for is "1".

That same PeriodID in the results table has 83,50 & 100 for measures 1, 2 & 3. I just need the textboxes in the form to display these values.

I just don't know how to do it so i'm asking for help.

 

[jdraw]

inq80,

There are tutorials at RogersAccessLibrary that show you how to design tables and relationships based on a thorough description of the business and processes. Each tutorial takes about 30-45 minutes to work through. But what you learn can be applied to any database. I think you would benefit considerably by working through 1 or 2 of these tutorials.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/Tutorials/ConsolidatedWidgetsDesign.zip
http://www.rogersaccesslibrary.com/Tutorials/CaterItDesign.zip

Normalization

Good luck.

 

[inq80]

Thanks jdraw, will certainly have a look at those.

I might need it as well as I'm starting to get the impression that what im asking is not possible.

 

[inq80]

Well my relationship table now looks different.

 

[inq80]

The results table you suggested Gizmo is just like my original one when i first started, except yours logs numbers, were as mine had text instead due to how i set up the tables.

Your excel walk through is excellent as well. It's pretty much a written description of my last 10 days or so.

I'm still at a lost on whether the form I've been trying to make is possible though...