Promting user for a value and then using that to search for records

[lodgey47]

I have records in a table that contain many fields 3 of which are text fields that I use for storing a year eg 2002,2003 etc. A record may only have a value in the 1st text field whereas other recods may have values in 2 or all 3 text fields. The problem I have is I want to select records that have the same year in any one of the text fields. For example:

Record 1 may have text fields containing: 2002, 2003, 2004
Record 2 may have text fields containing: 2003, 2004, 2005
Record 3 may have text fields containing: 2002
Record 4 may have text fields containing: 2002, 2004, 2006
Record 5 may have text fields containing: 2006, 2007

In the query I want the user to enter one year value using the prompt [Please enter required year] eg 2004 and for the query to then select records that contain that year value in any of the text fields. In this example records 1, 2 and 4 would be selected. If the user entered 2007 only record 5 would be selected. How do I do this?

Grateful for any help!

 

[EMP]

In query Design View, put the same prompt for each of the three fields, but put it in three separate criteria rows (see the attached image.)


Three fields for the years. Your data are stored in a spreadsheet-like table. That is, they are not normalized. Unlike Excel, Access is a relational database application. You should put the years in one column in a separate table and link the two tables with a one-to-many relationship. This way, you will need only one prompt.

^

 

[lodgey47]

I changed my query to prompt the user as you suggested but when I put a value in the first year field the query runs and doesn't prompt for the 2nd and 3rd year fields. Can I prevent the query running until all 3 fields have filled in?
Using a table to store the year values and then creating the one to many relationship doesn't seem to work either. The database has evolved over time and I think I should have spent more effort thinking about the best design/structure but unfortunately I didn't!. Originally each record included just one invoice total amount for a given year. The user then wanted to store invoice total amounts for upto 3 years so I created text fields for storing the year with a corresponding number field holding the invoice total amount. As with the example shown in my original posting a record may have 3 text fields containing 2002,2003,2004 (each with their own invoice total amounts) whereas another may hold values for 2000,2002,2006. What I need to do for example is extract the records that have values for 2002 i.e. text box 1 in the 1st record and text box 2 in the 2nd record. I hope this all makes sense! Any ideas?

 

[ColinEssex]

If you are saying you have a new field for each year (like a spreadsheet) then your design is wrong.

You should have a field called "Years" (not "year" as its a reserved word) and each record should be on a new line so your table looks like this

RecordNo. . . . . . .Years
1. . . . . . . . . . . . .2004
1. . . . . . . . . . . . .2006
1. . . . . . . . . . . . .2002
2. . . . . . . . . . . . .2001
2. . . . . . . . . . . . .2006

etc

Col

 

[gemma-the-husky]

your table design may not be the best. however you can get what you want

if you only have 3 text columns, then you need 3 criteria rows.

in one row test column1
in the next test column 2
in the next test column 3

when your criterai are in the same row you are

doing crit1 AND crit2 AND crit3

this way you are doing
crit1 OR crit2 OR crit3

 

[Jon K]

From your 1st post:
"In the query I want the user to enter one year value using the prompt [Please enter required year] eg 2004 and for the query to then select records that contain that year value in any of the text fields."

From your 2nd post:
"a record may have 3 text fields containing 2002,2003,2004 ........... whereas another may hold values for 2000,2002,2006. What I need to do for example is extract the records that have values for 2002 i.e. text box 1 in the 1st record and text box 2 in the 2nd record."

From the above, what I have gathered is if the user enters e.g. 2002 when prompted, the query should search each of the three fields for that number.

If this is the case, there is no need for the query to prompt the user three times for the same number. EMP's suggestion should work.


See the attached database, which uses EMP's suggestion. When you run the query and enter 2002 once, the query should return these results.

ID	FieldA	FieldB	FieldC	OtherFields
1	[b]2002[/b]	2003	2004	aaa
2	2000	[b]2002[/b]	2006	bbb
6	2000	2001	[b]2002[/b]	fff

.

 

[lodgey47]

Yes EMP solution does work however what I would like to do now is group together in a report all the invoice values for any year the user wants so I may have:

Record 1: 2002 £50, 2004 £100, 2006 £10
Record 2: 2004 £125, 2005 £100, 2007 £50
Record 3: 2002 £100, 2003 £100, 2004 £125
Record 4: 2003 £100, 2006 £125, 2007 £25

If the user selects 2004 then the report would group Rec1 and Rec 2 and Rec 3 together and the invoice total would be the sum of Rec 1 text field 2, invoice value + Rec 2 text field 1, invoice value + Rec 3 text field 3, invoice value i.e £100+£125+£125 = £350
If the user selects 2006 then Rec 1 text field 3, invoice value + Rec 4 text field 2, invoice value ie £10+£125 = £135.
Can I do this?
I hope it all makes sense!
Thanks