Query Expression to autofill given other criteria

[SimplyDemented]

I have a grades_table with data regarding a grade a person gets.
It contains like Name, Date, Grade, Grader_Name, Month, Year...

I have another table (grader_table) that contains the Grader_Name, and how many Grades that grader needs done in a given month (i'm using two numbers to indicate month and year).

What I would like to do is I have a form where all info for the grades_table is entered. When the query is run, I would like to have the month and Year field use the date given to auto fill numbers.

So I picture it as like 6/5/07 is given, so the query records the date, but also stores the 5 and 7 in other columns. I tried this in my query for i think its title - Month: Month([Date]) but it says there one too many ")".

I have a feeling I may also need help with the actual retrieval of a months score since it will cross tables, but this is the big question for now. Any help is appreciated.

 

[kodiak385]

This may not completely solve your problem but...

Whether this actually helps you or not depends on whether you're trying to simply display the date info on your form, or store that info to a table.

If you want to just display a numeric representation in separate fields on your form, you can create two unbound controls with

=Format([Date],"m")

and

=Format([Date],"y")

for your month and year, respectively.

Unfortunately, if you're trying to actually STORE this to a table or query (which I'm pretty sure is what you're asking here), I'm not sure how to help you. Actually, does anyone know how to store a calculated value to a table? 'Cause I'd like to know, haha. Go me.

How do you plan to use this? Why do you need to store month and year separately? You should be able to extract this information in a query using the format functions above whenever you need it.

Hope this helps. I'm pretty new at this myself.

 

[kodiak385]

Yeah, just reread your post and I'm pretty sure I'm answering the wrong question, haha. Sorry. Hope it helps a little at least.

 

[SimplyDemented]

Heh heh, any help is appreciated.

Here is what I need it to do.
I have my table that contains grades that are given on tons of dates. Each Grader needs to have a certain amount done each month to hit their monthly goal. The problem I see is down the road when I write a report that covers multiple months.

So for example, lets say Joe Shmoe has like 5 grades done in sept, 4 done in october, and 5 in Nov. If run a report that says how are his goals from sept-nov and use full dates, I would imagine I would have to assign a date to the goal. So like 9/1/07 his goal is 5, 10/1/07 goal is 4, and 11/1/07 his goal is 5. This would give me bad data though since if I run the report from say 9/15-11/15 it wouldnt show the goal assigned on 9/1/07.

So I figured what I could do to make it simpler is store the months and year seperate and use simple comparisons. This way, I can say include all goals where month >=9 and <=11 and year =07. This way the goal doesnt have a day attached, but is rather looked at for the whole month.

Hope I did an ok job explaining that.

Edit:
I'm thinking of how I could extract it and do the comparisons then. I am also very new but will play around with it that way. Still would love to know how to store it though.

 

[kodiak385]

Give this a try...

So based on what you've told me, I put together a real quick model database that I think addresses your problem. I'm not sure if this is exactly how you have it set up, but I think it's close. You'll notice I eliminated the Month and Year columns from the grades table, and instead simply extracted that information using a query for the report. I left month as a numeric value in the goal table just to keep things simple in joining that. Is this what you're looking for?

 

[SimplyDemented]

Kodiak,

Thanks a lot for the attachment! That's going a little above and beyond there.

Unfortunately my work doesn't allow downloading zip's and i'm sure a db may throw a few red flags. I will check it out when I get home later tonight.

Thanks again!

 

[kodiak385]

No worries Company security policies are fun, right? lol

 

[Niroth]

Unfortunately, if you're trying to actually STORE this to a table or query (which I'm pretty sure is what you're asking here), I'm not sure how to help you. Actually, does anyone know how to store a calculated value to a table? 'Cause I'd like to know, haha. Go me.

Never tried it myself, but maybe an append or update query might do that for you?

Try making a query from your normal table.

In the new query, calculate the value to want.

Create another table with an extra field for the calculated value to want to add in.

Use an update query to populate that field.

Just a suggestion, not sure it works, quite new to this myself.

 

[SimplyDemented]

Thanks Niroth, anything is worth a shot.

I may actually have this right on the edge of working now. I have been playing with it for awhile and I have two queries that work. One returns the right goals, the other returns all the information I need from the people. What would be great is if my report can call the 2 queries independant of one another, but access won't let me.

So I have my one query return a bunch of names, scores, whatever from 1 DB. The other query is just needed to be called from 1 text box in the report to return the goal from another DB. Problem is if I put them both in the designview of the report, they combine with each other and I get tons of duplicate data. If I don't put them together in the design view, I get an error saying the report can't find the goal field anywhere.

*sigh* very aggravating.

 

[kodiak385]

Tried using DLookUp()?

Yeah, I feel your pain. I know there have been plenty of times where I wish I could just pull fields from a secondary query. You can use a DLookUp() function to call information from another query if you need it, it's just a bit more of a pain to do.

Have you had a chance to check out the example DB I sent? If that's not what you're looking for, it might be helpful to see an example of how your tables are set up and what you want your report to look like.

 

[SimplyDemented]

Kodiak,

Unfortunately I won't be out of work until 10PM EST, I work 11 hours days 4 days a week. Honestly, I have a feeling if I see an access DB once I get out of here, I may break whatever monitor I may be looking at...

I will check out the one you sent tomorrow while i'm off. Curiousity I am sure will end up dragging me back to this issue even if I am not at work.

On a side note, I checked out DLookup and it actually seems pretty promising. I was able to return a goal like I wanted... BUT it can only return one it seems. So in my DB that has say 3 goals for 3 different months, I can't sum them up within the report. I imagine there may be a way to do a loop in VB to call dlookups for all cases where a name appears and sum up that total. Think that would work? I don't know VB, but it's another idea I can try.

 

[SimplyDemented]

... there is a DSum function...

HAHAHAHA~!

This fixed one of the big problems I had. I was unaware of this function and can now call a dsum from the report to tally up the goals based on the Forms input!

*sigh* I knew it was gonna have some super simple solution. I still have some tweaking to do to fix little things here and there but this gets over the big hurtle of the 2 queries conflicting with each other.

 

[SimplyDemented]

Got it working. I appreciate the help from everyone.

I ended up kinda mixing and matching different ideas from the thread. I didn't bother saving the individual month and year in the DB that held the scores, but did hold it seperate in the DB with the goals. The report then pulls in all the scores using dates, breaks up the date range by using month() and year() functions, then uses those numbers to call this ugly little thing:

=DSum("[goal]","tbl_Management_Goals","Management = " & '[text18]' & " AND Month >= " & '[Begin_Month]' & " AND Month <= " & '[End_Month]' & " AND YEAR >= " & '[Begin_Year]' & " AND YEAR <= " & '[End_Year]' )

One thing it doesn't handle is crossing years very well when i was doing error checking. For what it's needed for I highly doubt that would be an issue, and if it did come up, I have 12 months to tweak it if I am even still here.

Thanks again for everything!

 

[kodiak385]

Haha, nothing like a messy string of code to get the job done. Glad you got it working.