Parameter query

[GeorgeChr]

I have designed a query wich contains the following columns: name, september, october, . . . , august.
I want to create a parameter so that when i open the query i'll be able to see only one month instead of all.
can i do that?
I only know to create parameters for one column not for the whole query..
thanx

 

[boblarson]

I have designed a query wich contains the following columns: name, september, october, . . . , august.
I want to create a parameter so that when i open the query i'll be able to see only one month instead of all.
can i do that?
I only know to create parameters for one column not for the whole query..
thanx

If you are only wanting one month, you should NOT have columns for each month, but a column with the data and a date column where you can set the criteria.

 

[GeorgeChr]

the query is based on a table called "monthy fees" which demonstrates the money paid each month by each student. If i do what u say then I think that I will have to type the student name 12 different times in the name column.. Im I right? That's why I created all these different columns because i couldn't think of a better solution..

 

[boblarson]

I think you need to look into NORMALIZATION as it sounds like you have repeating columns (1 for each month) and that is definitely NOT the way the design should be. It will only cause you major headaches trying to "band-aid" fix things, such as trying to get the information you want here.

I know that isn't what you wanted to hear, but that is reality. You can work with us to get the design right and get it redesigned, but right now you have a major problem in trying to get the data out.

Right now you could make things visible or invisible based on the selection, but it isn't going to be pretty, nor is it going to be efficient.

 

[GeorgeChr]

I have another idea come up right now: What if i create 12 different tables (one for each month) and relate all the "name" columns together somehow and therefore create a query wich draws all the payments from all the tables into one so that i can see only one student? you think that is possible? I'm trying to find a way but i don't know that much about access nor vba..

 

[boblarson]

I have another idea come up right now: What if i create 12 different tables (one for each month) and relate all the "name" columns together somehow and therefore create a query wich draws all the payments from all the tables into one so that i can see only one student? you think that is possible? I'm trying to find a way but i don't know that much about access nor vba..

Instead of doing that, why not create ONE table with all of the months in there and add a date field, if you don't already have it, and then you just use the query to pull the correct month information. That means NO multiple columns in the table for each month:

tblPayments
PaymentID - autonumber (PK)
StudentID - LongInteger (FK from Student Table)
PaymentDate - Date/Time (short date)
PaymentAmount - Double