Distinct Query, on one field with return on multiple fields

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Thank you in advance for your assistance..

I am creating a Query on 2 non-related tables, just so
that I will have one record set.
Using Select Distinct to prevent the list of records from
being duplicated for every occurrence of an additional
record in the other DB.
BUT How do I create a select distinct query that will
produce a distinct output of all fields based on one field.

Thought of using 2 queries, but thought I would ask the
experts if there is a better way.
 
Attempting to query on "non-related tables" will ALWAYS return multiple
records. It is sometimes referred to as a Cartesian Join or a Cartesian
Product. The result will always bring back one record in Table2 for each and
every record in Table1. Thus, if Table1 has 3 records and Table2 has 8
records, your query will bring back 24 records.
 
OK --- SO HOW DO I GET AROUND IT ????
Do I need to add a linking field to the tables and create
a relation, or is there another way???
 
YES, You need to have some field that links the tables either in one-to-one
or one-to-many.
 
-- OK, THANK YOU, LET ME ASK ANOTHER QUESTION ---
Do I need to have both table in the query??
Here is the situation;
table1 will be used as the source in the CBX dropdown
(source may be the wrong term, when the dropdown box is
clicked the list of choices will come from table1, and any
new entries will be saved to table1) Other then an ID
field that is all thats in table1, The Form is used to
create a record in table2....

OK, This sounds like a simple one to many relation, and
only the ID field from table1 should be saved to
table2.... BUT I can't visualise it and it has been
DBASEIII since I did relational DB's, and even then I was
only a beginner...
 
Does this help
Columns
Name Type Size
Q_ID Long Integer 4
Qs Text 100
Table Indexes
Name Number of Fields
PrimaryKey 1
Fields:
Q_ID Ascending
Columns
Name Type Size
QForm_ID Long Integer 4
DateEntered Date/Time 8
DateModified Date/Time 8
Active Yes/No 1
Q1 Text 100
Q2 Text 100
Q3 - Q25 Just like these
Table Indexes
Name Number of Fields
PrimaryKey 1
Fields:
QForm_ID Ascending
 
Well, that was a little more than I expected, but think I can work with it.
Just the table names and the field names would have sufficed. Anyway, if you
join the tables on Q_ID = QForm_ID, then you will have a one to one
relationship. If you need a one to many relationship, then you will need to
add an appropriate Foreign Key to one of the tables (whichever one is the
child table) and join the tables on that field -- it's quite common to name
the Foreign Key the same as the Primary Key of the parent table.
 
THANK YOU, I have now got this working... am sure to have
many more issue before I complete this DB.. Should I
start a new post on each, most will deal with forms and
VBA...
again thank you you've been a big help..
 
I'm glad you got it working the way you need. Yes, please open a new post
for each new issue you need to ask about.
 
Back
Top