Listbox/Combobox Stumper

[Randomblink]

I have two tables.

table 1:
-> tblEquipment
Fields:
-> EqID - Autonumber Field
-> EqName - Name of Equipment

table 2:
-> tblServices
Fields:
-> SrID - Autonumber Field
-> SrName - Name of Service


Now then, to my problem...
How would I create ONE control...
Listbox or Combobox
That would have TWO columns.
In column Number 1, I want it to hold the ID field of the Equipment OR Service being listed...
In column Number 2, I want to show Equipment AND Services...

Then as a User selects an Item, it checks the ID field, uses that ID field to <insert many different actions here>.

How can I do this? I have several controls I will need to do this to if it can be done, so it has to be something I can genericize (make generic) and re-use on all the needed controls.

I can ALMOST work my mind around how to make this happen...then I get dizzy and pass out...<chuckle>

Help if you can! Thanks in advance.

 

[The_Doc_Man]

This is a concept problem that can lead you down a very large, long, meandering garden path with no end in sight.

You are trying to build an ambiguous controlsource, which is not possible in Access - nor is it wise in ANY database product. WORSE, you are trying to merge the results of this ambiguity in two different ways, both of which define too many constraints.

The problems I see are in the relationships between the two tables. You have two autonumber tables. They will have overlapping numbers as PKs. So if you select a row from your combo box that is Equipment OR Service, for which table is the number a primary key?

Then, in the next column, you have Equipment AND Service, so you have two prime keys there, too. In this case, they are BOTH going to be present. But if there is a relationship between Service and Equipment that doesn't depend on the prime keys (e.g. it operates through a linking table as an intermediary because it is a Many-to-Many case - many items of equipment can apply to many services, in other words), you will get an overconstrained combination. This table you describe cannot be constructed because of the conflicts that are being generated.

You have a design that is asking for Access to do the impossible. Redesign your concept. Rethink what you are asking.

It might be a small adjustment that is needed. But try to look at it from the basis of, When I select an entry, what am I going to store? When I see a list of entries to select, how will that list be selected? When the entry has been selected and stored, what is going to happen to it next?

Now, as a suggestion, search this forum for the concept of "cascading combo boxes" as a way to get information about two tables that might be somehow related. Don't try to do this in one combo box from two tables at once. It will never fit.

 

[Randomblink]

I FIGURED IT OUT!!!!!

SELECT [SkID], [SkName] AS [Prerequisite], "Skill" AS [Type] FROM [tblSkills]
UNION SELECT [vanID], [vanName] AS [Prerequisite], "Vantage" AS [Type]
FROM [tblVantages] ORDER BY [Prerequisite] ASC;

This did it...
As I select something from the list, I will merely reference the TYPE field to tell which table to make my interaction through...!!!

FREAKING SWEET!

Thanks Doc_Man anyway...I agree that there could be problems, but I couldn't give up...I am planning on using this for a great deal of things! This is excellent...! And I did it using SQL!

See, I wouldn't normally be so excited, but, I have had Access classes from local schools that literally taught me nothing. The instructors were merely pushing through another Corporate group. They taught how to create tables, queries, forms, and reports...but they never taught anything...know what I mean?

I never learned anything from those classes that I hadn't already figured out. Then I thought if I took a VB class that would help, and the instructor was the worst instructor I have ever dealt with. He would be in the middle of teaching something, he would say, this is how this works...then it wouldn't work...so he would send us out on break while he tried to figure it out. Most of them time, when we got back from our break, he would move onto something else saying, "Well, I don't know what is wrong, but this doesn't work."

He would also not teach us many of the basic functions of VB because, as he put it, "He didn't use it in his job, so we wouldn't need it either." ?????

Anyway, I am 90% self-taught...or, taught by "Access World Forums" and figuring out a UNION query just makes me friggin excited! I am writing basic SQL statements too...I learned that from this board and tweaking on my own fun databases!

So, enough rambling, I was major excited when I figured this out...thanks for this board!

ps Doc_Man, do I get kudos or something for this one?

 

[Alexandre]

Pat, I never used outer joins for tables relationships, but only for queries.
What are the advantages of using outer joins in the very structure design, over using them only in queries when needed?

 

[Randomblink]

Actually...let me explain

Ok...
Actually, I am building a db for a game system I play. I hope to move my database skills to gaming web apps eventually...but that is another story.

The App has two tables.
One is for Character Skills.
(eg., Driving, Computer Operation, etc.)

One is for Character Vantages.
(eg., Bad Eyesight, Ambidexterity, etc.)

Some skills require you to know another skill, as a pre-requisite, in order to learn them. Some skills require you to have a certain advantage, like Good Eyesight in order to learn Piloting. So, I am trying to create ONE listbox with ALL Skills and Vantages so that a user who is inserting a new Skill to the database can select what Pre-requisites that Skill has. The there is another listbox on the Skill entry form that shows Pre-requisites for that Skill AND shows Defaults that skill has (i.e., If you know Karate, you can probably use the Boxing skill without ever having officially trained in boxing, ergo Default).

A skill has a huge list of other fields that matter to it, but are not even listed in a Vantage table. And Vice versa.

Pat, you mentioned a 1-1 table. I tried to understand your example, but I am lost.

I am posting a copy of what I built so far. PLEASE, feel free to pop it open and critique or give an example of what a 1-1 is. I don't understand how you would create one...what linking structure is used, etc.

If you have some examples, please post them. Thanks for the help so far.

As for figuring out the UNION query, I have been practicing on my game database, because I want to use new ideas, but can't really tease fate by using my main database with actual Government data in it, so I work on something for fun that doesn't matter if I hurt it.

Anyway, thanks for all the help.
I was going to attach it, but it is 253kb, and therefore too large.
If someone would like a copy to check my work, I would appreciate it...thanks!

 

[Randomblink]

Complexity Galore

I do have tables...
I am including a gif of my relationships.
The two union queries are off to the right.

Pat, you being the guru guy, you wouldn't be willing to look it over and give me some feedback? I respect your opinion ALOT and would be quite happy if I could get the "Guru's" opinion on my form design...the listbox'es and comb-boxes as well as the queries and placement of data...

I am pretty excited to be able make this db do this much. I am hoping to get more classes under my belt and start work on a web app utilizing java, sql, vb, and the things I have learned from working on Access...

But anyway...here is my relationship mix...
My total db is 253kb (roughly, give or take a kb) while zipped.
If anyone would like a look-see, I would love to share it...

Thanks again for all the input...

Pat, back to my question...
LEFT JOIN and 1-to-1 relationships...?
I have pretty much ALWAYS used the default relationship, excepting a rare query here and there...
Could you point me to a good tutorial on relationships...? Or share some of that knowledge brainiac...


THE FOLLOWING THREADS ARE ALSO INVOLVING THE DB:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=32900

 

[The_Doc_Man]

The part that bothers me is this: If you do a UNION query of two tables containing AUTONUMBER fields as prime keys, the resulting UNION query cannot contain a prime key - because the PK fields, being autonumbered independently, will overlap. Which immediately disqualifies it from use in anything that requires a PK for lookup purposes. I'm concerned that your list box or combo box isn't doing what you think it is doing. What I'm wondering is, what AREN'T you seeing in your UNION query?

I believe that you are asking for seriously anomalous behavior down the road. I'm not going to be a hard-nose about this, but I have to point out that you are still treading on dangerous ground. So I have trouble agreeing with the kudos. I don't think you have solved your problem, you have only masked it.

Oh, what the heck - kudos for figuring out on your own what a UNION query does and how to apply it.

But no kudos for trying to apply it in the context you described.

 

[Randomblink]

KUDOS AT LAST!

My Combobox is populated by this UNION QUERY:
SELECT [SkID] AS [PreID], [SkName] AS [Prerequisite], "Skill" AS [Type]
FROM tblSkills
UNION SELECT [vanID] AS [PreID], [vanName] AS [Prerequisite], "Vantage" AS [Type]
FROM [tblVantages]
ORDER BY [Prerequisite];

Then, I run this:
Private Sub cmbPrereq_AfterUpdate()

Select Case cmbPrereq.Column(2)
Case "Skill": sqlAction sqlLink("Insert", "tblLINK_tblSkill-PrerequisiteSkill", "SkID", "preSkID", SkID, cmbPrereq, Me)
Case "Vantage": sqlAction sqlLink("Insert", "tblLINK_tblSkill-PrerequisiteVantage", "SkID", "prevanID", SkID, cmbPrereq, Me)
End Select

lstPrerequisites.Requery
End Sub

Ok...

Here is how I can tell which AUTONUMBER to run...
Remember, when I create my Union Query I create a new field PER TABLE...

SELECT [SkID], [SkName] AS [Prerequisite], "Skill" AS [Type] FROM [tblSkills]
UNION SELECT [vanID], [vanName] AS [Prerequisite], "Vantage" AS [Type]
FROM [tblVantages] ORDER BY [Prerequisite] ASC;

This makes that Row in the Query show which table this came from...Then I use the Select Case to find that Column, and interact with the correct table based on the column...

Now do I get an unfettered kudos Mr. Doc Man...????

<chuckle>

Hey, I like to think I am picking this stuff up here...

 

[Randomblink]

Still No Kudos...

I have put together quite a little db and still no kudos...

ho hum...ah well...