Copy data to another query

**PC Datasheet** · Nov 12 '05, 09:05 PM

Re: Copy data to another query

Create a query that joins the two queries and set the appropriate criteria and
you have essentially copied records from one query to another.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Daniel Tan" <danieltan@time .net.my> wrote in message
news:6a6a9040.0 403020624.48720 300@posting.goo gle.com...[color=blue]
> Are there anyway to copy rows of records from one query to another
> query and then hide the records in source query ? Pls advise. Thanks.
>
> Regards,
> Daniel[/color]

**Brendan Reynolds** · Nov 12 '05, 09:05 PM

Re: Copy data to another query

Records don't live in queries, they live in tables. Queries are just a view
of selected records. So the way to change which of the two queries displays
those records is to change whatever value it is that determines which query
displays the records.

For example, if you have two queries, one of which displays records WHERE
SomeField = 'X' and the other displays records WHERE SomeField = 'Y', then
you change the query that displays a particular record by changing the value
of SomeField in that record from 'X' to 'Y'.

--
Brendan Reynolds

"Daniel Tan" <danieltan@time .net.my> wrote in message
news:6a6a9040.0 403020624.48720 300@posting.goo gle.com...[color=blue]
> Are there anyway to copy rows of records from one query to another
> query and then hide the records in source query ? Pls advise. Thanks.
>
> Regards,
> Daniel[/color]

**Daniel Tan** · Nov 12 '05, 09:07 PM

Re: Copy data to another query

Brendan, do you mean just swap the value of Somefield from one query
to another after i create a new query that comprise of the 2 sub
queries ?

Regards,
Daniel

"Brendan Reynolds" <brenreyn@remov ethisindigo.ie> wrote in message news:<BG11c.525 8$rb.64013@news .indigo.ie>...[color=blue]
> Records don't live in queries, they live in tables. Queries are just a view
> of selected records. So the way to change which of the two queries displays
> those records is to change whatever value it is that determines which query
> displays the records.
>
> For example, if you have two queries, one of which displays records WHERE
> SomeField = 'X' and the other displays records WHERE SomeField = 'Y', then
> you change the query that displays a particular record by changing the value
> of SomeField in that record from 'X' to 'Y'.
>
> --
> Brendan Reynolds
>
> "Daniel Tan" <danieltan@time .net.my> wrote in message
> news:6a6a9040.0 403020624.48720 300@posting.goo gle.com...[color=green]
> > Are there anyway to copy rows of records from one query to another
> > query and then hide the records in source query ? Pls advise. Thanks.
> >
> > Regards,
> > Daniel[/color][/color]

**Brendan Reynolds** · Nov 12 '05, 09:09 PM

Re: Copy data to another query

No. You can not swap a value from one query to another, because a query does
not contain any values.

To change which records are displayed in a query, you have two choices - you
can change the data (which lives in the table, not in the query), so that it
meets the criteria of a different query, or you can change the SQL statement
that defines the query. Following on from my previous example, if QueryA
displays all records WHERE SomeField = 'X', while QueryB displays all
records WHERE SomeField = 'Y', and I want to change that around, I could do
it as follows ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Q ueryA")
qdf.SQL = "SELECT * FROM SomeTable WHERE SomeField = 'Y'"
Set qdf = db.QueryDefs("Q ueryB")
qdf.SQL = "SELECT * FROM SomeTable WHERE SomeField = 'X'"

I might be able to offer a clearer explanation if I had more information -
are we really talking about displaying queries directly, or are we talking
about data displayed in form controls, such as combo boxes or list boxes,
based on those queries? This would, in my experience, by a much more common
requirement - filter one combo box or list box based on the item selected in
another, or allow a user to choose items from one list box and display those
choices in another. Is that what we're talking about here?

--
Brendan Reynolds

"Daniel Tan" <danieltan@time .net.my> wrote in message
news:6a6a9040.0 403030911.5f0a6 714@posting.goo gle.com...[color=blue]
> Brendan, do you mean just swap the value of Somefield from one query
> to another after i create a new query that comprise of the 2 sub
> queries ?
>
> Regards,
> Daniel
>
> "Brendan Reynolds" <brenreyn@remov ethisindigo.ie> wrote in message[/color]
news:<BG11c.525 8$rb.64013@news .indigo.ie>...[color=blue][color=green]
> > Records don't live in queries, they live in tables. Queries are just a[/color][/color]
view[color=blue][color=green]
> > of selected records. So the way to change which of the two queries[/color][/color]
displays[color=blue][color=green]
> > those records is to change whatever value it is that determines which[/color][/color]
query[color=blue][color=green]
> > displays the records.
> >
> > For example, if you have two queries, one of which displays records[/color][/color]
WHERE[color=blue][color=green]
> > SomeField = 'X' and the other displays records WHERE SomeField = 'Y',[/color][/color]
then[color=blue][color=green]
> > you change the query that displays a particular record by changing the[/color][/color]
value[color=blue][color=green]
> > of SomeField in that record from 'X' to 'Y'.
> >
> > --
> > Brendan Reynolds
> >
> > "Daniel Tan" <danieltan@time .net.my> wrote in message
> > news:6a6a9040.0 403020624.48720 300@posting.goo gle.com...[color=darkred]
> > > Are there anyway to copy rows of records from one query to another
> > > query and then hide the records in source query ? Pls advise. Thanks.
> > >
> > > Regards,
> > > Daniel[/color][/color][/color]

**Daniel Tan** · Nov 12 '05, 09:26 PM

Re: Copy data to another query

HI Brendan

Yes i would like to display my data on a form. I would like if i
click on a check box then the record would be copied to another table
and source record would disappeared.

Regards,
Daniel

"Brendan Reynolds" <brenreyn@remov ethisindigo.ie> wrote in message news:<TLE1c.546 1$rb.64458@news .indigo.ie>...[color=blue]
> No. You can not swap a value from one query to another, because a query does
> not contain any values.
>
> To change which records are displayed in a query, you have two choices - you
> can change the data (which lives in the table, not in the query), so that it
> meets the criteria of a different query, or you can change the SQL statement
> that defines the query. Following on from my previous example, if QueryA
> displays all records WHERE SomeField = 'X', while QueryB displays all
> records WHERE SomeField = 'Y', and I want to change that around, I could do
> it as follows ...
>
> Dim db As DAO.Database
> Dim qdf As DAO.QueryDef
>
> Set db = CurrentDb
> Set qdf = db.QueryDefs("Q ueryA")
> qdf.SQL = "SELECT * FROM SomeTable WHERE SomeField = 'Y'"
> Set qdf = db.QueryDefs("Q ueryB")
> qdf.SQL = "SELECT * FROM SomeTable WHERE SomeField = 'X'"
>
> I might be able to offer a clearer explanation if I had more information -
> are we really talking about displaying queries directly, or are we talking
> about data displayed in form controls, such as combo boxes or list boxes,
> based on those queries? This would, in my experience, by a much more common
> requirement - filter one combo box or list box based on the item selected in
> another, or allow a user to choose items from one list box and display those
> choices in another. Is that what we're talking about here?
>
> --
> Brendan Reynolds
>
>
> "Daniel Tan" <danieltan@time .net.my> wrote in message
> news:6a6a9040.0 403030911.5f0a6 714@posting.goo gle.com...[color=green]
> > Brendan, do you mean just swap the value of Somefield from one query
> > to another after i create a new query that comprise of the 2 sub
> > queries ?
> >
> > Regards,
> > Daniel
> >
> > "Brendan Reynolds" <brenreyn@remov ethisindigo.ie> wrote in message[/color]
> news:<BG11c.525 8$rb.64013@news .indigo.ie>...[color=green][color=darkred]
> > > Records don't live in queries, they live in tables. Queries are just a[/color][/color]
> view[color=green][color=darkred]
> > > of selected records. So the way to change which of the two queries[/color][/color]
> displays[color=green][color=darkred]
> > > those records is to change whatever value it is that determines which[/color][/color]
> query[color=green][color=darkred]
> > > displays the records.
> > >
> > > For example, if you have two queries, one of which displays records[/color][/color]
> WHERE[color=green][color=darkred]
> > > SomeField = 'X' and the other displays records WHERE SomeField = 'Y',[/color][/color]
> then[color=green][color=darkred]
> > > you change the query that displays a particular record by changing the[/color][/color]
> value[color=green][color=darkred]
> > > of SomeField in that record from 'X' to 'Y'.
> > >
> > > --
> > > Brendan Reynolds
> > >
> > > "Daniel Tan" <danieltan@time .net.my> wrote in message
> > > news:6a6a9040.0 403020624.48720 300@posting.goo gle.com...
> > > > Are there anyway to copy rows of records from one query to another
> > > > query and then hide the records in source query ? Pls advise. Thanks.
> > > >
> > > > Regards,
> > > > Daniel[/color][/color][/color]

Copy data to another query

Copy data to another query

Comment

Comment

Comment

Comment

Comment