Query not showing correct information

[Mich2too]

We are trying to create a query to pull information from a linked table from a SQL database and a table we have created in Access. The SQL table contains a waiting list of people waiting for classes. The table (Removal)we have created has only two fields in it....the ID (which matches the ID in from the SQL field) and a yes/no check box to indicate if the name needs to be removed from the waiting list.

I have set up the query so that all of the fields from the SQL waiting list are shown and only those fields from the Removal table that match are shown. The criteria for the query is that the yes/no box must indicate no.

The problem is that when new people are added to the waiting list, they do not appear in the query.

I am not familiar with code, so you may have to lead the blind a bit if that is where we need to go with this issue.

THANKS for any assistance any of you can give us.

 

[Mich2too]

Basically....it seems that my original query is not the problem even though I've done everything that has been suggested.

So....to sound like a broken record....does anyone have suggestions as to how to update the Remove table with ID#s for those folks who have newly been added to the SQL waiting list?? This is seems to be the REAL issue...not my query.

 

[Golom]

OK ... our apologies for the gender confusion (and Leslie is also a member of the smarter sex.)

If you can locate the section of code in which records are being added to WaitingList then you can add new records to your [WaitingList - Remove These] table without having it open. Given that there are numerous ways that you may be doing that (i.e. adding a record), we can give you only the most general of directions but here goes.

If you are adding a new record by appending it to a recordset using the "AddNew" method then your code will look something like

rs.AddNew
rs![Inquiry_ID] = SomeIDValue
... setting other fields in the new record ...
rs.Update

following this you could just add the lines

CurrentDB.Execute _
"INSERT INTO [WaitingList - Remove These] (Inquiry_ID, Remove ) " & _
" VALUES ('" & SomeIDValue & "', 0 )"

Where "0" sets "Remove" to "False". Use "-1" if you want "True".

In general, that second block of code (the INSERT INTO ...) will put the new Inquiry_ID into your table. Your challenge is to find out where the insert is happening and put that code in place.

Oh ... and by the way ... the single quotes around SomeIDValue in the INSERT INTO imply that it's a text field. If it's numeric then remove the single quotes.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[lespaul]

And we weren't trying to be disparaging....we're just tring to help!

Here's a link that may help you understand the joins and what they mean and how they work:

Understanding SQL Joins

So, there's a main SQL table where people are put on the mailing list, but in some separate process people are added to the 'Remove' table (in Access) which indicates whether or not they need to be removed from the waiting list. Is that correct?

Once they have been added to the Remove table and are marked 'Yes' then they need to be removed from the waiting list table. Does that correctly describe the situtation?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Mich2too]

Sorry for having sounded nasty. It just seemed as if no one really read what I was saying.

The main SQL table is the one and only place where people are put into the waiting list and is linked into the Access file. This linked table can NOT be editted to in any way. So a Remove table was created so that we could use it as an indicator that these folks were no longer interested, had taken the training or were removed for some other reason. The connection between the two tables is the ID#.

SQL linked table fields....ID#, Name, phone, training wanted
Remove table fields.....ID#, yes/no

As there is no way to conveniently use the waiting list in the SQL base program, we have set up an Access file and created a form so that we can scroll through the waiting list, track comments and remove people from the list. Currently, we are scrolling through 400+ records to get to 70+ folks who are still in need of classes. We want to limit this form to show ONLY those who are truly still waiting for classes.

We are wide open to suggestions as long as we are not recreating the wheel. It also has to be user friendly (or automatic)as the users hardly know how to use computers.

Have been thinking that maybe an update query might work to look at the SQL table and the Remove table and update the Remove table with ID#s that are not already there. Thoughts...suggestions???

 

[Mich2too]

Golom....just now read your last post....I had skipped down to the end and only read Lespaul's post. Have printed out your post and will be attempting this step.

Will keep you posted if I get it to work or not. Thank you again!! (maybe my migraine will go away soon???)

 

[Golom]

...maybe my migraine will go away soon?

If it does, let me know. I've had one since I started programming and SQL has only made it worse.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Mich2too]

Golom,

Well....the migraine went away for a day or two. Unfortunately, our office building was burglarized this past weekend and have been dealing with that all week. Just now am getting to trying what you had suggested about the record set.

As my knowledge of SQL is very nill...I'm not exactly sure WHERE to be looking to put that Insert stuff in. Should this be going into the query or into the form which is used to view this information and the comments associated with contact attempts?

 

[Golom]

As I said before, the "INSERT INTO" needs to be placed in your code following the place where a new record is being added to the WaitingList table in the SQL database. Unfortunately, as I also said, there are many ways to add a record to a table and without seeing how your application is structured, it's almost impossible to say exactly where that is.

The challenge here is not (or mostly is not) the SQL but rather it is locating the place or places where new records are added and creating code to add exactly corresponding records to the local table at the same time.

The other option is just to chicken out and give them a "Update Local Table" button that just adds records from the SQL table to the local table that don't currently exist in the local table. How practical that is will depend on factors associated with the information and work flows in your system.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Mich2too]

Okay....one major problem with this....we are not able to make ANY changes to the SQL database. The ONLY place we make any changes is in the Access file where the SQL tables are linked, queries and forms are created.

So....am thinking that maybe the easiest way might be take your suggestion of the "update local table button" idea is our only way to do this. Create the update query and have it run automatically when the form used to view the waiting list and matching comment/call history. Am thinking of the 'automatic' part only because the less human intervention that is needed will mean less people running to me all the time asking....'should I click on that button???'

Refresh my memory.....is there a way to run the update query and skip the warnings that you are going to update the table...yada yada yada????

THANKS!!! I REALLY DO appreciate everyone's help. It's been a rough couple of weeks but you all saw me through it!!THANKS!!!!

 

[PHV]

Have a look at the DoCmd.SetWarnings method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886