Pulling all data from a row that has a check box checked and saving it to a new sheet

[thracken]

Hello,

I can someone tell me how to make a append query pull data from one data sheet and save it to another. I have 2 fields that i need it to check and if the box is checked then i need all data from that row ( ID and everything in that row) to be pulled and saved to a new sheet.The data sheet ( source with about 250 rows ) it will have to pull the information from has about 20 columns and I only need the information to be copied to the new data table if the box is checked in the landlord or investor field, but i will need all the data on that row on the new sheet, and the new sheet is bigger than this one below and too large to upload

thanks for any help

ID	Name	Phone #	Landlord	Investor	email
1	tom	222-222-2222	yes		ou81@yahoo
35	john	333-333-3333		yes	ou84@yahoo

[pbaldy]

Sounds like you want an append query. Create a query that pulls the records you want from the source table. While in design view, click on the Append icon in the ribbon, select the destination table. You may need to specify the destination fields if they have different names.

[thracken]

ok i am missing something, I have made the Append query and selected 2 fields that must be checked ( some will be checked and some will not be ) and I do not need to use any information from that field that is not checked. Now the checked fields I need all the information from that row the checked information is in. So is there a way to have it pull all the information from that row that is checked and put it into the new table with more than 2 fields. I have 21 fields of information i need pulled from the source sheet and put on the new sheet and the only information that will determined what rows will be use are the 2 fields that are checked.

[pbaldy]

What's the SQL of your query? You would include the 21 fields in the grid. If the 2 fields are part of those, then the criteria would just be on them. If not, you'd have 23 fields in the grid, with only 21 having fields specified in the destination table.

[thracken]

INSERT INTO [Landlords/Investors] ( [Land Lord], INVESTOR )
SELECT [Bandit sing list].Landlord, [Bandit sing list].INVESTOR
FROM [Bandit sing list];

the data sheet has 250 different people on it, and I have about 30 fields of information i will need to pull from the source sheet and put on the new sheet that only needs 19 of the 30. There are 2 fields that will determine what information will be used if they are checked only ( landlord and investor ).

[June7]

I wonder why all the data has to be duplicated and not just saving the ID?

[pbaldy]

Did you try adding the other fields to the grid? I'd expect something like:

INSERT INTO [Landlords/Investors] (Field1, Field2, Field3, [Land Lord], INVESTOR )
SELECT Field1, Field2, Field3, Landlord, INVESTOR
FROM [Bandit sing list]
WHERE Landlord = True OR INVESTOR = True

[thracken]

the list has such a big number of people on it and only a few will be needed. all i am trying to do is pull information from the ID from the source that has a check in that one field and save to a different sheet with just that information on it. Did I understand your question right ?

[June7]

Why would just the SELECT query not serve purpose? Why must records be written to table? What are you going to do with table that query could not be used for?

[pbaldy]

I'm going to get out of June7's way.

[thracken]

ok i did try what you gave me and it gave me an error " syntax error in insert into Statement"

this is what i used to see if it would work : INSERT INTO [Landlords/Investors] (id,First Name, Last Name, [Land Lord], INVESTOR )
SELECT id,First Name,Last Name, Landlord, INVESTOR
FROM [Bandit sing list]
WHERE Landlord = True OR INVESTOR = True

As i am new its hard for me to see what i am doing wrong, and thanks for your time and help

[thracken]

I will be adding information to the table from another source later ( a form ). I am not sure if I understand you right, but if there is an easier way to get the information then I am all for it. I just don't know much and am trying to learn what i should and should not do.

and i did go back and select the fields that would be needed but how can i make it only select the row with a check in the box for investor and landlord ? it works good except it selects all the information from every row.

SELECT [Bandit sing list].ID, [Bandit sing list].[Phone number], [Bandit sing list].[First Name], [Bandit sing list].[Last Name], [Bandit sing list].[E-mail], [Bandit sing list].Landlord, [Bandit sing list].INVESTOR, [Bandit sing list].State, [Bandit sing list].[Area looking in], [Bandit sing list].[Will you do any repairs], [Bandit sing list].[how fast can you close], [Bandit sing list].[will you have cash or loan], [Bandit sing list].[how much equity do you need], [Bandit sing list].[SQ FT], [Bandit sing list].[how many properties are you looking for], [Bandit sing list].[up dated], [Bandit sing list].[Date of lead], [Bandit sing list].[bedroom/bath], [Bandit sing list].Notes, [Bandit sing list].[maximum purchase amount]
FROM [Bandit sing list];

[June7]

Wow, reading that response hit me with deja vu.

Paul has given you example to follow but sounds to me like:

1. you are duplicating data

2. don't have data structure clearly identified

3. maybe should be using a form/subform arrangement for data entry

Maybe this site will help http://www.rogersaccesslibrary.com/

Your field names have spaces so must enclose with []. Avoid use of spaces and special characters/punctuation (underscore is exception) as well as reserved words in naming convention.

[thracken]

ok thanks for your help

[June7]

I might have been editing my post as you were posting.