Help with a query with an IN statement (1 Viewer)

[MatS]

I have a problem that I have been trying to solve and have looked all over for a solution with no success. I know the solution is probably simple and I am probably over thinking this. Here is the situation:

tblNeeds: I have a series of fields with different items that a customer might need to have fixed. Items are labeled TR, OC, BR, etc. These are actually blank fields so any of those codes can go into any of the fields. Next to each filled is a Yes/No field that start off as unchecked (not done) but will eventually be checked (done), but not all at once.

Through the process of this application some of these "jobs" will be done. I have gone through the transaction table and pulled out the codes for these transactions and through a series of queries eventually get it down to a text string that puts all the completed codes into that string. In the query the String looks like this: 'BR', 'OC', 'TR'. That is the order they were done. Side note, I have actually made a second query to change the output to: ('BR', 'OC', 'TR') but don't think that is really what is going to work, but I have tried. The query I think I need to use is titled qry_Needs_List with two fields: CustID and List

What I am trying to do is do an Update query to change the first "Need" field to true. The Yes/No field is titled Need1_Filled (the field with the code is Need1).

I have tried doing an update query with tblNeeds linked to the query via CustID. I have the Need1_Filled set to be updated to True/Yes. There is no criteria set for this column. The next column I have it set up as Need1 from tblNeeds with the criteria like this: In (Select List from qry_Needs_List).

Other things I have tried: double parentheses In ((Select List from qry_Needs_List)).
putting List in brackets, think I also did the same to the query.
since the table and query are linked just doing In (List), also doing double parentheses and/or brackets.
Probably have tried other things but can't remember.

If I copy the query result - 'BR', 'OC', 'TR' directly into the criteria section: In ('BR', 'OC', 'TR'), the query will change the the Yes/NO field to Yes. That is the only thing that has worked but obviously isn't a doable solution. I think the query works but I am not pulling the code part correctly but can't figure out how. If I put "Select List from qry_Needs_List" in a blank query in the SQL code format, it pulls the correct info. So it has to be the criteria section.

Any thoughts or suggestions would be greatly appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

Can you post your table structure, so we can get a better picture of your database?

 

[The_Doc_Man]

I am not at all clear from the verbal description but it SOUNDS like your tables are not properly normalized. If they are, then maybe I didn't read this correctly. However, your structure perhaps should be

1. Customer table - Customer ID of some sort, name, address, other info. The ID could possibly instead be a JOB ID if your customers have repeat business, in which case you would have a separate customer data table to link to the job.

2. A sub-task table - linked to the customer or job, you would have a narrow table that names the things to be fixed with your Y/N fields and any other data such as a date of action and even a person's name or initials to record who did the work. (Not clear from the description whether that matters to you.)

Having a record with multiple tasks in the same record is "spreadsheet thinking" but Access isn't a spreadsheet so you are fighting the formatting. You will fight the processing of this data a lot.

One major issue is to remember that with Access, there is a split between storing data internally for optimum processing and displaying that data for ease of presentation or use. I don't know if this advice helps you or not because some aspects of that are not clear.

If I may suggest, think really hard about the description of your PROCESS. Don't sweat the query (yet). Famous mathematician Niklaus Wirth (the father of the PASCAL computer language) once said that at least 80% of all programming problems stem from incorrect data design. Perhaps you should look at your data layout. IF you are not familiar with database normalization, I very strongly urge you to look into that before going much farther than your current situation.

If so, you can search this forum for "normalization" (because this IS a database forum.) If you search the general web, you must search for "database normalization" because at least four other disciplines use "normalization" in a meaningful way.

 

[MatS]

Here is tblNeeds.

I do have a customer table, but it has all the info on it (Name, address, phone, etc)
tblNeeds is just the CustId and the needs and if they have been met.
There is also a WorkCompleted table. The work completed has it's own PK (workNum) with the work details attached to it (who, time, etc.)
The tblNeeds is used for a form, so that I can show what is needed and what has or hasn't been done. All I am concerned with is what needs to be done and if it is still "open" or "finished".

Thanks.

 

[The_Doc_Man]

OK, it is as I thought. You have what is called a "repeating group" which is one of the most common normalization errors for people changing from spreadsheets to relational databases. That table IS wide - 14 repetitions wide. It SHOULD be deep. Each entry in the table should be

< CustID, NeedID, NeedName, NeedFilled > and then you have an entry in this table for each need for each customer - but never more entries than there are needs.

You will CERTAINLY drive your self crazy trying to manipulate those needs if they remain spread out like that.

I VERY STRONGLY suggest that you need to study normalization of database tables. Your current path is not in a good direction.

By the way, I should have gotten back to you sooner but we had major storms in our area and I have been outside picking up branch debris from our trees. I've got 3 cans and 2 bags of lawn trash so far. (Plus a sore back.) Tomorrow, out comes the chain saw.

 

[MatS]

Thanks for getting back to me, don't worry about the time delay, things happen and this isn't your job (I think).

I do understand normalization and I would normally set that up like you suggested. What I don't know how to do is use a normalized table like you suggest to then transfer it to a form. I don't know how to take a table with an infinite number of records and place those records on a form. That is why I chose to do it that way, so that I could then link each record to a specific field on a form.

 

[Minty]

You would use a subform linked back to the customer.
Think of it like an order header and multiple order lines, it's exactly the same design concept.

The subform would normally be a thin continuous form to display 1,2,5,100 child records.

 

[mike60smart]

Hi
Can you upload a zipped copy of the database?

 

[MatS]

Thanks Minty. I ended up setting it up like you said and how I set up other subforms on the form. I had an "image" of how I wanted it to look on the form and doing it that way wasn't what I had in mind. However, doing that way got it to work and allowed me to add some other things into the subform that I was contemplating doing, so that was the best solution. Thanks for making me see it a different way.

Also, thanks to everyone else who tried to help me with this.

On a side note another problem has arisen. When I was changing form/subform/field properties I have change something that is now preventing the form text box from passing it's value into queries as a criteria. I keep getting the enter parameter message and I wasn't getting that until I changed some settings. That field wasn't part of this first problem and it is breaking queries that I hadn't touched since the beginning. Anyone have any ideas?

Update: Nevermind, I deleted the text box and re-added it and it works fine now. All queries are functioning properly.