selecting union query results

[Jaybird]

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?

 

[Maurice]

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.

 

[Jaybird]

Maurice,

Thanks for your response... My combo box is bound to a table. I'm using
the results of the union query for reference, in order to avoid duplicate or
bad data from being created. Perhaps I should start over... This query is
designed to workaround an un-normalized table. This Order Entry table
contains four part number fields and their associated quantities,
thicknesses, etc. This query very neatly gives me each part number for each
order number from this table. I want to select from these results the
specific part number (and qty, thickness, etc.) and have that information
transferred to another table (let's call it Process Table). Unfortunately,
no matter which part number I select, only the first part number associated
with that order number is passed to the Process Table. This is simply a
workaround until I can convince my boss to normalize the database, but I need
it to work because the Order Numbers are being split into the separate Part
Numbers when they are being processed. If I use only the Order Number to
reference the parts, it will seem as if all four of them have been processed,
which may or may not be the case.
--
Why are you asking me? I dont know what Im doing!

Jaybird

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.
--
Maurice Ausum

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?

 

[Maurice]

How is the combo set up? Are you referring to the first column as the bound
column (which should be right if you only have one field)...
--
Maurice Ausum

Maurice,

Thanks for your response... My combo box is bound to a table. I'm using
the results of the union query for reference, in order to avoid duplicate or
bad data from being created. Perhaps I should start over... This query is
designed to workaround an un-normalized table. This Order Entry table
contains four part number fields and their associated quantities,
thicknesses, etc. This query very neatly gives me each part number for each
order number from this table. I want to select from these results the
specific part number (and qty, thickness, etc.) and have that information
transferred to another table (let's call it Process Table). Unfortunately,
no matter which part number I select, only the first part number associated
with that order number is passed to the Process Table. This is simply a
workaround until I can convince my boss to normalize the database, but I need
it to work because the Order Numbers are being split into the separate Part
Numbers when they are being processed. If I use only the Order Number to
reference the parts, it will seem as if all four of them have been processed,
which may or may not be the case.
--
Why are you asking me? I dont know what Im doing!

Jaybird

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.
--
Maurice Ausum

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?

 

[Jaybird]

Yep. It's bound to the first column. The Union query (posted earlier) is
referenced directly in the row source property of the combo box. The other
data in the query is used to populate some text boxes in the form. The
selection of the specific data is essential in order to determine which parts
are actually in production. Like I said earlier, I think my problem is that
the combo box references the order number only, when I actually need a unique
identifier for each part number on the order. Still, I don't know how to get
around this without creating a new table. If I do that, I might as well
normalize the whole database. Something more than a workaround...
--
Why are you asking me? I dont know what Im doing!

Jaybird

How is the combo set up? Are you referring to the first column as the bound
column (which should be right if you only have one field)...
--
Maurice Ausum

Maurice,

Thanks for your response... My combo box is bound to a table. I'm using
the results of the union query for reference, in order to avoid duplicate or
bad data from being created. Perhaps I should start over... This query is
designed to workaround an un-normalized table. This Order Entry table
contains four part number fields and their associated quantities,
thicknesses, etc. This query very neatly gives me each part number for each
order number from this table. I want to select from these results the
specific part number (and qty, thickness, etc.) and have that information
transferred to another table (let's call it Process Table). Unfortunately,
no matter which part number I select, only the first part number associated
with that order number is passed to the Process Table. This is simply a
workaround until I can convince my boss to normalize the database, but I need
it to work because the Order Numbers are being split into the separate Part
Numbers when they are being processed. If I use only the Order Number to
reference the parts, it will seem as if all four of them have been processed,
which may or may not be the case.
--
Why are you asking me? I dont know what Im doing!

Jaybird

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.
--
Maurice Ausum


:

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?

 

[Maurice]

Jay,

Let me put things in perspective here so that I get your question correct
(please fire if it is incorrect..)

You have created a union query based on four tables
You have a from bound to this query
You have a combo bound to one of the fields of this query

You have some kind of action that needs to trigger when the combo goes to a
specific record you are searching for.

Why don't you try making an 'extra' combofield ->unbound to the recordset
with the source being the queryfields you need just to identify the specific
record.

in the AfterUpdate of the combo you place the code to find the record you
are looking for. When the record is found you could use an append query to
append it to the table.

Because you are already updating the table (i don't know how, appendquery or
looping through fields or something like that) there must be something going
wrong when it is appending only those specific parts from the query.

So for the additional info place your action when you have found the specific
record(s).

hth
--
Maurice Ausum

Yep. It's bound to the first column. The Union query (posted earlier) is
referenced directly in the row source property of the combo box. The other
data in the query is used to populate some text boxes in the form. The
selection of the specific data is essential in order to determine which parts
are actually in production. Like I said earlier, I think my problem is that
the combo box references the order number only, when I actually need a unique
identifier for each part number on the order. Still, I don't know how to get
around this without creating a new table. If I do that, I might as well
normalize the whole database. Something more than a workaround...
--
Why are you asking me? I dont know what Im doing!

Jaybird

How is the combo set up? Are you referring to the first column as the bound
column (which should be right if you only have one field)...
--
Maurice Ausum

Maurice,

Thanks for your response... My combo box is bound to a table. I'm using
the results of the union query for reference, in order to avoid duplicate or
bad data from being created. Perhaps I should start over... This query is
designed to workaround an un-normalized table. This Order Entry table
contains four part number fields and their associated quantities,
thicknesses, etc. This query very neatly gives me each part number for each
order number from this table. I want to select from these results the
specific part number (and qty, thickness, etc.) and have that information
transferred to another table (let's call it Process Table). Unfortunately,
no matter which part number I select, only the first part number associated
with that order number is passed to the Process Table. This is simply a
workaround until I can convince my boss to normalize the database, but I need
it to work because the Order Numbers are being split into the separate Part
Numbers when they are being processed. If I use only the Order Number to
reference the parts, it will seem as if all four of them have been processed,
which may or may not be the case.
--
Why are you asking me? I dont know what Im doing!

Jaybird


:

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.
--
Maurice Ausum


:

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?

 

[Jaybird]

Howdy Maurice!

Well, what I have is an Order Entry table that contains information about up
to four different part numbers. We track these part numbers by the Order
Number. You can imagine that if these parts were to be processed separately,
there could be problems identifying them. What I'm attempting to do is
create a form at each step of the operation that refers directly to the Order
Entry table via a combo box. John Spencer's neat little query splits each
order number up by part number, but doesn't create a unique identifier for
each row, so the combo box doesn't know which Order Number I'm want to
select, and just picks the first one. (At least that's what I think it's
doing.) The only method I know of to assign unique identifiers to each row
of information from the query involves appending to a table, assigning a
reference, and then referring to that table from my combo box. I'm a little
worried about the speed at which this information can be updated. I'm toying
with the idea of attaching an Append To query on the After Udate event of my
main form that will populate both the old version of the table and the new
normalized version. Then I can refer to the normalized version of the Order
Entry table in my combo box. Duplicating data like that gives me the
heebie-jeebies, though. Not good practice. How, for instance to I keep the
data in synch? If I delete or add to one table or another, how will the
change affect the information in the other table? Anyhow, if you have any
suggestions or information that will enhance my understanding of the issue,
please let me know.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jay,

Let me put things in perspective here so that I get your question correct
(please fire if it is incorrect..)

You have created a union query based on four tables
You have a from bound to this query
You have a combo bound to one of the fields of this query

You have some kind of action that needs to trigger when the combo goes to a
specific record you are searching for.

Why don't you try making an 'extra' combofield ->unbound to the recordset
with the source being the queryfields you need just to identify the specific
record.

in the AfterUpdate of the combo you place the code to find the record you
are looking for. When the record is found you could use an append query to
append it to the table.

Because you are already updating the table (i don't know how, appendquery or
looping through fields or something like that) there must be something going
wrong when it is appending only those specific parts from the query.

So for the additional info place your action when you have found the specific
record(s).

hth
--
Maurice Ausum

Yep. It's bound to the first column. The Union query (posted earlier) is
referenced directly in the row source property of the combo box. The other
data in the query is used to populate some text boxes in the form. The
selection of the specific data is essential in order to determine which parts
are actually in production. Like I said earlier, I think my problem is that
the combo box references the order number only, when I actually need a unique
identifier for each part number on the order. Still, I don't know how to get
around this without creating a new table. If I do that, I might as well
normalize the whole database. Something more than a workaround...
--
Why are you asking me? I dont know what Im doing!

Jaybird

How is the combo set up? Are you referring to the first column as the bound
column (which should be right if you only have one field)...
--
Maurice Ausum


:

Maurice,

Thanks for your response... My combo box is bound to a table. I'm using
the results of the union query for reference, in order to avoid duplicate or
bad data from being created. Perhaps I should start over... This query is
designed to workaround an un-normalized table. This Order Entry table
contains four part number fields and their associated quantities,
thicknesses, etc. This query very neatly gives me each part number for each
order number from this table. I want to select from these results the
specific part number (and qty, thickness, etc.) and have that information
transferred to another table (let's call it Process Table). Unfortunately,
no matter which part number I select, only the first part number associated
with that order number is passed to the Process Table. This is simply a
workaround until I can convince my boss to normalize the database, but I need
it to work because the Order Numbers are being split into the separate Part
Numbers when they are being processed. If I use only the Order Number to
reference the parts, it will seem as if all four of them have been processed,
which may or may not be the case.
--
Why are you asking me? I dont know what Im doing!

Jaybird


:

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.
--
Maurice Ausum


:

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?

 

[Maurice]

Hi Jay,

I understand your problem. The solution you are opting for is an option.
Because you are dealing with unnormalized data you now find out what kind of
problems you bump into. As you are saying it is not good practice to double
the data but in this case you can't get out of it. If you plan to use this
option you migth have to agree a certain point into where the 'old' data
isn't valid anymore. So set an endpoint as to where the normalized table
should get preference. If you don't opt for this option (setting the new
table as pref.) you will always keep running into problems. That's what I
call 'plastering'. Difficult decision to make.

I'd suggest go for the append for a while and take for granted that there
will be a period where you have to stop 'feeding' the old table.
Normalization will pay of in the end.

--
Maurice Ausum

Howdy Maurice!

Well, what I have is an Order Entry table that contains information about up
to four different part numbers. We track these part numbers by the Order
Number. You can imagine that if these parts were to be processed separately,
there could be problems identifying them. What I'm attempting to do is
create a form at each step of the operation that refers directly to the Order
Entry table via a combo box. John Spencer's neat little query splits each
order number up by part number, but doesn't create a unique identifier for
each row, so the combo box doesn't know which Order Number I'm want to
select, and just picks the first one. (At least that's what I think it's
doing.) The only method I know of to assign unique identifiers to each row
of information from the query involves appending to a table, assigning a
reference, and then referring to that table from my combo box. I'm a little
worried about the speed at which this information can be updated. I'm toying
with the idea of attaching an Append To query on the After Udate event of my
main form that will populate both the old version of the table and the new
normalized version. Then I can refer to the normalized version of the Order
Entry table in my combo box. Duplicating data like that gives me the
heebie-jeebies, though. Not good practice. How, for instance to I keep the
data in synch? If I delete or add to one table or another, how will the
change affect the information in the other table? Anyhow, if you have any
suggestions or information that will enhance my understanding of the issue,
please let me know.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jay,

Let me put things in perspective here so that I get your question correct
(please fire if it is incorrect..)

You have created a union query based on four tables
You have a from bound to this query
You have a combo bound to one of the fields of this query

You have some kind of action that needs to trigger when the combo goes to a
specific record you are searching for.

Why don't you try making an 'extra' combofield ->unbound to the recordset
with the source being the queryfields you need just to identify the specific
record.

in the AfterUpdate of the combo you place the code to find the record you
are looking for. When the record is found you could use an append query to
append it to the table.

Because you are already updating the table (i don't know how, appendquery or
looping through fields or something like that) there must be something going
wrong when it is appending only those specific parts from the query.

So for the additional info place your action when you have found the specific
record(s).

hth
--
Maurice Ausum

Yep. It's bound to the first column. The Union query (posted earlier) is
referenced directly in the row source property of the combo box. The other
data in the query is used to populate some text boxes in the form. The
selection of the specific data is essential in order to determine which parts
are actually in production. Like I said earlier, I think my problem is that
the combo box references the order number only, when I actually need a unique
identifier for each part number on the order. Still, I don't know how to get
around this without creating a new table. If I do that, I might as well
normalize the whole database. Something more than a workaround...
--
Why are you asking me? I dont know what Im doing!

Jaybird


:

How is the combo set up? Are you referring to the first column as the bound
column (which should be right if you only have one field)...
--
Maurice Ausum


:

Maurice,

Thanks for your response... My combo box is bound to a table. I'm using
the results of the union query for reference, in order to avoid duplicate or
bad data from being created. Perhaps I should start over... This query is
designed to workaround an un-normalized table. This Order Entry table
contains four part number fields and their associated quantities,
thicknesses, etc. This query very neatly gives me each part number for each
order number from this table. I want to select from these results the
specific part number (and qty, thickness, etc.) and have that information
transferred to another table (let's call it Process Table). Unfortunately,
no matter which part number I select, only the first part number associated
with that order number is passed to the Process Table. This is simply a
workaround until I can convince my boss to normalize the database, but I need
it to work because the Order Numbers are being split into the separate Part
Numbers when they are being processed. If I use only the Order Number to
reference the parts, it will seem as if all four of them have been processed,
which may or may not be the case.
--
Why are you asking me? I dont know what Im doing!

Jaybird


:

You've used Union Select which returns distinct records from the various
select statements. The output of a Union is a read only unless you create a
table from it. You've used it as a rowsource for a combo. Is this combo an
unbound field or is it bound to a certain field. In the latter it could be
the case that the combo is expecting a numerical value and gets a textvalue
instead.
--
Maurice Ausum


:

Dear All,

With some help from John Spencer I've come up with a query that does exactly
what I want... except that when I use it as the row source for my combo box
in a form, I cannot select the specific record I want. I'm guessing that my
selection criteria aren't specific enough, but I'm unsure of how to go about
fixing it.

Here's my SQL:

SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 1] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY 1]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number1]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 2] as
[Thick], [Order Entry].[Part Number 2] AS [Part Number], [Order Entry].[QTY2]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 2]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 3] as
[Thick], [Order Entry].[Part Number 3] AS [Part Number], [Order Entry].[QTY
3] AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number 3]) Is Not Null))

UNION SELECT [Order Entry].[Order Number], Contacts.CompanyName, [Order
Entry].[Material Spec:], [Order Entry].Alloy, [Order Entry].[Thick 4] as
[Thick], [Order Entry].[Part Number1] AS [Part Number], [Order Entry].[QTY4]
AS QTY
FROM Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID
WHERE ((([Order Entry].[Part Number4]) Is Not Null))
ORDER BY [order entry].[order number];

What this does is combine the contents of four fields from a table, ignoring
nulls, ordered by [Order Number]. I'm guessing that my combobox is only
looking for results that match the Order Number I select and ignores the fact
that I'm looking for a unique record. How can I do this?