Access Noob: Script Error Not Transfering Data from One Table to Another

[CPThompson]

Hey all,



I'm new to Access and trying to help out a free clinic by fixing some of the issues they have been experiencing with their database. I've been trying to figure out this last one for a while now and just can't find any traction. Basically, I want the query to take data from one table, "Clients Info," to another preexisting table, "Discontinued_Clients." However, whenever I run the query it just loads the Client data into a new mysterious table one line long?? I can upload screenshots if that would help. Here's the query script:

INSERT INTO Clients_Discontinued ( [Unique ID], [Last Name], [First Name], Birthdate, Sex, [Start Date], [Appointment Frequency], [Last Seen], Dosage, [Current in Treatment?], [Dr Barclay is PCP], [Insurance Provider], [Issuer/Group], [Member ID], [Number of Scripts Remaining], [Needs New Prior Auth], Requirements, Notes, [Treatment Location], [Appointment Schedule], PriorAuthMonths, [Date of Last Prior Auth], [Appointment Time], [Last ASI Screening], [Medical Conditions] )
SELECT [Client Info].[Unique ID], [Client Info].[Last Name], [Client Info].[First Name], [Client Info].Birthdate, [Client Info].Sex, [Client Info].[Start Date], [Client Info].[Appointment Frequency], [Client Info].[Last Seen], [Client Info].Dosage, [Client Info].[Current in Treatment?], [Client Info].[Dr Barclay is PCP], [Client Info].[Insurance Provider], [Client Info].[Issuer/Group], [Client Info].[Member ID], [Client Info].[Number of Scripts Remaining], [Client Info].[Needs New Prior Auth], [Client Info].Requirements, [Client Info].Notes, [Client Info].[Treatment Location], [Client Info].[Appointment Schedule], [Client Info].PriorAuthMonths, [Client Info].[Date of Last Prior Auth], [Client Info].[Appointment Time], [Client Info].[Last ASI Screening], [Client Info].[Medical Conditions]
FROM [Client Info]
WHERE ((([Client Info].[Unique ID])=[Forms]![Discontinue_Frm].[Unique_id_txt]));


What do you think I need to change?

Thanks in advance!!

[June7]

That SQL looks good. What do you mean by 'one line long'? Do you mean a single record?

Why 'move' records? Why not just have a field in Clients Info to indicate client status? Can be a date/time or yes/no type.


BTW, advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Nor reserved words as names.

[CPThompson]

Thanks for the quick response! I agree that it would make more sense to just have yes/no for "Discontinued_Client" in the main table--that being said, the clinic I'm helping out have had it set up so that clients are transferred to a new table after being discontinued to reduce clutter in the old table (despite a sort function enabling the same utility). The Clients_Discontinued table worked for some time--there are some records already in it--but stopped working in the last year, probably because no one here knows how to use Access.

As for 'one line long,' this is what I mean:

I run the Clients_Discontinue_query with a client selected in a menu. Then instead of putting the data into the Clients_Discontinued table, this happens: http://imgur.com/cClVb6q

I have no idea where this table is housed or what it's called--it looks like it may be part of the Client_Discontinue_query query? Very confused.

And thanks for the advice on naming. I will keep that in mind.

[June7]

Image doesn't help much. All I can see is a table with a single record. Can't even see the table name.

How could you not know where the table is? How did you open it to get that screen shot?

Sorry, can't help unless I can review the db. If you want to provide, follow instructions at bottom of my post.