New Records into Query Results?

[burns863]

Hi,

I am unsure if this is even possible to do from within an Access SQL Query but here goes...

I already have a query which grabs specific records from the main table. Once the query has been performed I have a few VBA modules which export that data to dBase format for use with another piece of software.

I now need to grab some additional records from the main table using the same query but with different criteria. I have done this before using UNION SELECT and it works fine. My hurdle comes with the data manipulation I need to perform.

To sum it up simply, I need to take all records where

.[FIELD] = "Criteria" but double the results. ie, if the main table contains 10 records which match the criteria then I need the query results to show 20 (10 duplicate records). On top of that, I need to change one fields contents in each of the duplicate records so that they are slightly different to the record they were copied from.

Hopefully that makes sense. I understand I could do this sort of manipulation in my module code but (without going into it) it would make life easier if I could kick this out in the query.

Thanks

 

[vbaInet]

It cannot be done in a query. It can probably be manipulated in a report.

For records to be displayed, they must exist in the table. They will only be displayed for as many times they exist. You cannot create duplicate records in a query.

You can however insert those duplicate records in to the table again but that will be breaken one of the first rules of normalization, causing your table more harm than good.

 

[stopher]

Here's how to do it in a query....

Create a new table with only a single field. In that field put two records. It doesn't matter what the values of those records are but for simplicity, 1, 2 will do.

Add the new table to your query but do not create a join. Add the field from the new table if you like as it will act as an identifier.
You can change or create new output fields to include the identifier if you wish.

By not joining the original query with the new table you are creating a cartesian product. This new view will be every combination of records from your original source and records from your new source. So if your original source had 20 records and your new table has two records then the query will result in 40 records.

hth
Chris

 

[vbaInet]

This is the bit where I was referring to - trying to manipulate the duplicate records in the query.

On top of that, I need to change one fields contents in each of the duplicate records so that they are slightly different to the record they were copied from.

 

[burns863]

Here's how to do it in a query....

Create a new table with only a single field. In that field put two records. It doesn't matter what the values of those records are but for simplicity, 1, 2 will do.

Add the new table to your query but do not create a join. Add the field from the new table if you like as it will act as an identifier.
You can change or create new output fields to include the identifier if you wish.

By not joining the original query with the new table you are creating a cartesian product. This new view will be every combination of records from your original source and records from your new source. So if your original source had 20 records and your new table has two records then the query will result in 40 records.

hth
Chris

Thanks Chris. I *think* I get what you mean. I'll give it a go

 

[burns863]

This is the bit where I was referring to - trying to manipulate the duplicate records in the query.

Cool. Thanks for your help vbaInet