How to create table where field #2 is exactly the values of field #1 except one to many

[markr]

So for simplicity, say I have a table with one field, called ID. It has three records {ABC, DEF, GHI}. How can I use a query to end up with a table with ID and ID2 (new field) which will look like such:

ID	ID2
ABC	ABC
ABC	DEF
ABC	GHI
DEF	ABC
DEF	DEF
DEF	GHI
GHI	ABC
GHI	DEF
GHI	GHI

In other words - for each ID, there would be a corresponding ID2 equal to all ID values from the original table.

[CJ_London]

Welcome to the forum

think you need to provide a clearer example, ID fields should be unique, but your ID2 field also has duplicates. Also, you have shown your example data, but it not at all clear what the required outcome is

[markr]

Thanks Ajax. Sorry for not being clear. The table above is the required outcome. ID is a unique field. ID2 is a new field I would create from the query. Essentially where I'm struggling is putting it in that kind of layout where for each ID, there would be an ID2 equivalent to each ID. Original table could be something like:

ID	Sales
ABC	100
DEF	500
GHI	200

The desired outcome from a query should result in the table below:

ID	ID2
ABC	ABC
ABC	DEF
ABC	GHI
DEF	ABC
DEF	DEF
DEF	GHI
GHI	ABC
GHI	DEF
GHI	GHI

[Minty]

This can be achieved by using a "Cross Join".
Add your table into the query viewer twice, then select the ID field from each one then run the query.

[markr]

I knew the answer would be very simple but didn't realize it was THAT simple. Why I didn't think of that, I have absolutely no idea! Thanks!

[CJ_London]

Looks like what you want is all possible combinations

so perhaps a Cartesian join (i.e. no join)

SELECT T1.ID, T2.ID
FROM myTable T1, MyTable T2
ORDER BY T1.ID, T2.ID

Edit: Ah, beaten to it by Minty!