Help? Creating 'virtual table' to lay multiply data presentation

  • Thread starter Thread starter S Davis
  • Start date Start date
S

S Davis

Hello folks,

I have a query in Access that returns a set of data. This calls data
fromour central database, and also another table I created in Access
that is simply the numbers 1 through 12.

Bringing them both in shows each record 12 times as a result, once for
each month as it were.

ie.

database1...database2...database3...mytable1
1...2...3...1
1...2...3...2
1...2...3...3
1...2...3...4
1...2...3...5
etc.
1...2...3...12

Simple.

I'm calling data using MSQuery in Excel. I'd like to seperate its
dependence on Access and just hit our main database. The trouble is,
the 'mytable' from Access does not exist on our main database
obviously, and I'd still like to replicate all records.

How can I do this? My thought is a 'virtual table', but I'm not sure
on either the proper term for such a thing, or how to create it in the
SQL. I'm not even sure if this is possible to be honest as SQL only
selects fields, and I don't think you can define field data there....

Thanks,
 
what you are gettign it 12 tiems for each record is because of the
Cartesian product you are gettign because the new "mytable" cannot be
linked to the other tables what you have to do is give the mytable
thing you have dragged from excel an id then decide how this data is
relevant to the other tables

after that you set up the forigen keys and then add the values into
the forigen keys so the data in this table is then linked to the data
in the rest of the database

so basically since the mytable is not part of the main databse you
cannot query it in releation to the main database because it is not
linked and you get the Cartesian product

i hope this info helps

Regards
Kelvan
 
what you are gettign it 12 tiems for each record is because of the
Cartesian product you are gettign because the new "mytable" cannot be
linked to the other tables what you have to do is give the mytable
thing you have dragged from excel an id then decide how this data is
relevant to the other tables

after that you set up the forigen keys and then add the values into
the forigen keys so the data in this table is then linked to the data
in the rest of the database

so basically since the mytable is not part of the main databse you
cannot query it in releation to the main database because it is not
linked and you get the Cartesian product

i hope this info helps

Regards
Kelvan
Click to expand...

Sort of :)

I understand how the unlinked tables multiply data (cartesian
product)... was justhoping there was some way to reproduce this
behaviour without actually having a seperate mytable in the main
database. I *want* to have each record multiplied 12 times. If not, no
problem, I'll leave it in Access.
 
Back
Top