query from two different tables with no common themes

[Guest]

I am trying to generate a query that will do the following:
I want the records to return from two different tables.
From the TblJC, acct field, I want the records with acct number 2 and 4, and
From the TblDispatch, contact field, I want the records with contact numbers
1 and 6.
The result would have the account name, and phone numbers from the first
table,
and from the second table, will have a name and address.
Does that make sense?

TIA
Pap

 

[tina]

are you just randomly picking account name/ phone number records and name /
address records? or is there some rhyme and reason to the records you want
to pull from each table?

hth

 

[Guest]

At least in my mind there is a rhyme and reason. Ultimately what I am
shooting for is a report to give to our local emergency folks that will list
an order of things to do and call. The report would be nice to have so that
as people, numbers etc change we can just run a report for them. The content
of the report includes names and phone numbers of people at certain points in
the order, pagers at others, and orgs at still others.

Does that help?

 

[tina]

no, sorry. i guess i'm off my game tonight, but i can't come up with a
picture of what you're doing that allows me to offer any useful suggestions.
maybe somebody else will have something helpful to contribute. good luck
with it.

 

[David Cox]

The term "relational database" is used because the data it contains is
related. There may be a list of names, and of telephone numbers and tasks
and locations. Somewhere in the database there should be be a link between a
name and a telephone number, a name and a task, a task and a location. They
may be in the same record, or they may be in different records and linked
together. Usually these links are called keys.

I would expect to see records in one table containing fields like:
Thing_to_do_key, description of thing to do.

and another table:
Person_key, Name_of_person

these linked by a third table:

Thing_to_do_key, Person_key

In tis way we can relate things to do with the persons that are to do them.

Do you recognise this structure in your database, using different names for
the fields?

 

[Guest]

Well it may not be the best approach, and I sort of understand what you said.
If you have any suggestions I would love to hear them.
This is what I came up with:
SELECT [zlookup Positions].txtReportTitle, qryCommand.ChaplainCell,
qryCommand.ChaplainPhoneHome, qryCommand.ChaplainPhoneWork, [zlookup
Positions].txtPagernumb
FROM qryCommand RIGHT JOIN [zlookup Positions] ON qryCommand.txtPosition =
[zlookup Positions].txtPosition
WHERE ((([zlookup Positions].numPositionID)=1 Or ([zlookup
Positions].numPositionID)=2 Or ([zlookup Positions].numPositionID)=3 Or
([zlookup Positions].numPositionID)=4))
ORDER BY [zlookup Positions].numPositionID;

 

[John W. Vinson]

At least in my mind there is a rhyme and reason. Ultimately what I am
shooting for is a report to give to our local emergency folks that will list
an order of things to do and call. The report would be nice to have so that
as people, numbers etc change we can just run a report for them. The content
of the report includes names and phone numbers of people at certain points in
the order, pagers at others, and orgs at still others.

I still don't see anything which would indicate WHICH names, phone numbers,
pagers etc. you want included in the report.

That said - you may be able to create a UNION query selecting a few records
from this table, a few records from that, a few records from the other. See
the online help for UNION. You'll need to go to the SQL window to build this;
the query would be something like

SELECT [Lastname] & ", " & [Firstname], [Phone] FROM ThisTable
UNION ALL
SELECT [EmployeeName], [Phone] FROM ThatTable
UNION ALL
SELECT [LName] & ", " & [FName], [Pager] FROM tblPagers
UNION ALL
.... <etc>

The trick is that each SELECT must return the same number of fields and they
must be of the same datatype - not phone numbers in one SELECT and dates or
memo fields in another.

Alternatively, you might want to make up a Mail Merge document in Word with
the constant text and fields for names and numbers.

John W. Vinson [MVP]