Referring to Columns in a Table

[zashaikh]

Hello.



I am trying to create a query that refers to columns in a table. The parameters of this query will be displayed in a form.

I have a form called FrmAlpha. frmAlpha contains one Lookup field that is linked to tblMain. tblMain has three columns. Column(0) lists the name of a product. Column(1) lists an alternative spelling of the name. Column(2) lists another alternate spelling.

When I click a button on frmAlpha, I want it to run a query called qryAlpha.

This query looks for a number of fields across another table. However, I want to pull data only regarding the "Product." However, because of the alternative spellings of my products, I need to be able to search all alternative names at the same time. Therefore, my query would look something like this:


SELECT tblMain.Product, tblMain.NameVariant1, tblMain.NameVariant2
FROM tblMain
WHERE (((tblMain.Product)="~~~ProductName~~~")) OR (((tblMain.NameVariant1)="~~~AltName~~~")) OR (((tblMain.NameVariant2)="~~~AltName2~~~"));

I want to replace ~~~ProductName~~~, ~~~AltName~~~ and ~~~AltName2~~~ with something like: That refers to my frmAlpha lookup field which re-refers to a specific column in my table.

I hope that makes sense...

[aytee111]

Never use names more than once in a database. Add an autonumber field to the main product table and carry that number across to all your other tables which refers back to the name - which is now only in one place!

You can use wildcards in your criteria, such as Like "*" & Forms!formname!productname & "*" - or variations on that. Do a search on wildcards and see the different possibilities.

[zashaikh]

I know one is never supposed to use additional names. However I was not the one who created the database. And I am also not permitted to make extensive changes. Hence why I am going through the query.

And wildcards won't help me in this situation. For example, I could have the following: "Ben and Jerry" or "Ben & Jerry" or "BenJerry" which all refer to the same thing. I would not be able to write "Ben*" because then I would get returns of "BenFrank Hotdogs".

[rpeare]

Does each product have 3 name variants and always has 3 name variants or can there be null values? Or better yet, do you have a sample database because this seems like it should be a pretty easy problem to solve.

[zashaikh]

No. There are no nulls.

And I think I figured out a way around it. I am going to create a form with a DLookup. By using the Dlookup, I will enter only the main name of the Product, and the name variants will be automatically pulled (via the Dlookup) and placed into additional fields on my form. I will hide these fields for aethetic purposes. After than, I can set a query to run off of the fields (including the hidden ones

[ssanfu]

and the name variants will be automatically pulled (via the Dlookup) and placed into additional fields on my form.

Ummmmm..... you do know:
(from Help)

"The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain."

????

[zashaikh]

Yes. But I will use the Dllokup to pull data into my form. Ive done it before and it works fine. (I may not be doing a good job explaining it).

Bascially, I will use Dlookup to find a record in my table based on a specific value in that record. Then, the other values in the other columns will be placed in the appropriate fields in my form

[ssanfu]

Then, the other values in the other columns will be placed in the appropriate fields in my form

How?
I've done this using a combo box, but it can't be done using DLookup because DLookup returns 1 value!

[Micron]

If you want multiple fields to be returned to the form (even if it's just 2 alternate names) why not just use a Select query?

[zashaikh]

I want to use a select query. But I dont want to type all the possible alterantive names for each product. I want to type it once, and have Access automatically insert the other alternative names.

ssanfu. This is an example:

Private Sub cmbProductName_AfterUpdate()
If IsNull(DLookup("[Product Name]", "tblProducts", "[ Product Name] = '" & Me! cmbProductName.Text & "'")) Then
Me.AlternativeName1.SetFocus
Else
Me.AlternativeName1.Value = Me.AltName1.Column(1)
Me.AlternativeName2.Value = Me.AltName2.Column(2)
End If
End Sub

[orange]

As previously requested in the thread, post a copy of the database with some sample data.
It might also help if you could provide an example showing what you have, and what you want it to look like when processed successfully. This may be a case of a picture is worth a 1000 words (explaining doesn't seem to be working).

You are working with non-normalized data structure, so you'll have some custom coding and a one-only solution(in my view).