By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,399 Members | 984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,399 IT Pros & Developers. It's quick & easy.

Combo box choices using query

P: n/a
Hi.

I'm producing a database system for coursework and have run into a problem on
one of my tables.
I have 4 tables (members, cd's, vinyl's and loans). It's for a company who
loans out music products to it's members.
For the loans table, one of my fields is member ID, and i have made a query for
its row source: SELECT [Members].[Member ID] FROM Members; so that the user can
only choose existing member id's.
This works fine. The problem is that i want to do the same for Product ID, so
the user can only select existing vinyl id's AND cd id's in the same box.
I tried: SELECT [CD's].[Product ID], [Vinyl's].[Product ID] FROM [CD's],
[Vinyl's]; but this doesn't work, and only gives cd product id's. I looked up
union queries, I'm not sure whether this is the sort of thing I need.

Any help would be greatly appreciated.

Thanx, Donovan
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m17.aol.com...
Hi.

I'm producing a database system for coursework and have run into a problem on one of my tables.
I have 4 tables (members, cd's, vinyl's and loans). It's for a company who
loans out music products to it's members.
For the loans table, one of my fields is member ID, and i have made a query for its row source: SELECT [Members].[Member ID] FROM Members; so that the user can only choose existing member id's.
This works fine. The problem is that i want to do the same for Product ID, so the user can only select existing vinyl id's AND cd id's in the same box.
I tried: SELECT [CD's].[Product ID], [Vinyl's].[Product ID] FROM [CD's],
[Vinyl's]; but this doesn't work, and only gives cd product id's. I looked up union queries, I'm not sure whether this is the sort of thing I need.

Any help would be greatly appreciated.

Thanx, Donovan

Hi Donovan
If I understand you table structure, then I guess a union query is what
you're looking for. The problem is that they are slow, non-updateable and
may indicate a poor underlying database design.

******
Could you look at re-designing to give a single table of products with a
field ProductType to indicate whether the product is on CD or vinyl.
******
If you really have to look at union queries, they are something like:

SELECT [CD's].[Product ID] AS ProID, [CD's].[Product Title] AS ProTitle,
FROM [CD'S]
UNION
SELECT [Vinyl's].[Product ID] AS ProID, [Vinyl's].[Product Title] AS
ProTitle, FROM [Vinyl's]

Note the use of an alias (eg AS ProID) to make sure the names of the columns
from both select queries are identical, which would be needed if your field
names were different, say, "tblCds.CdTitle" "tblVinyls.VinylTitle"

Fletcher
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.