"pivoting" data with a querry

All,

I've got a table as follows:

[ItemNumber] [Supplier1] [Supplier2] [Supplier3] [Supplier4]
[Supplier5]

I need to generate a combo-box listing each of the five suppliers for an
Item Number. I've considered something like

[combo].[additem] dlookup (ItemNumber, Supplier1) - forgive the bad
programming!

but it seems a bit intensive for some low spec workstations

Is it possible to create a querry to base the combo box on?

thanks in advance

Jay Hallsworth

You shouldn't have 5 supplier fields. This isn't normalized. Can you change
your data structures to have a table with fields and records like:

ItemNumber SupplierID
A 1
A 2
A 7
B 1
B 4

This kind of setup allows any number of suppliers per ItemNumber and makes
your combo box a piece of cake.

--
Duane Hookom
MS Access MVP
--

"jay" wrote in message
...
All,

I've got a table as follows:

[ItemNumber] [Supplier1] [Supplier2] [Supplier3] [Supplier4]
[Supplier5]

I need to generate a combo-box listing each of the five suppliers for an
Item Number. I've considered something like

[combo].[additem] dlookup (ItemNumber, Supplier1) - forgive the bad
programming!

but it seems a bit intensive for some low spec workstations

Is it possible to create a querry to base the combo box on?

thanks in advance

Jay Hallsworth

Hi Duane,

Many thanks for your reply.

If only ... I've been thinking that ever since I've started looking at the
table! ... unfortunately the table isn't mine, it's actually a table I'm
getting from Sage Line100. I'm constantly in bewilderment as to the logic of
Sage's programming!

Regards,

Jay



"Duane Hookom" wrote in message
...
You shouldn't have 5 supplier fields. This isn't normalized. Can you
change your data structures to have a table with fields and records like:

ItemNumber SupplierID
A 1
A 2
A 7
B 1
B 4

This kind of setup allows any number of suppliers per ItemNumber and makes
your combo box a piece of cake.

--
Duane Hookom
MS Access MVP
--

"jay" wrote in message
...
All,

I've got a table as follows:

[ItemNumber] [Supplier1] [Supplier2] [Supplier3] [Supplier4]
[Supplier5]

I need to generate a combo-box listing each of the five suppliers for an
Item Number. I've considered something like

[combo].[additem] dlookup (ItemNumber, Supplier1) - forgive the bad
programming!

but it seems a bit intensive for some low spec workstations

Is it possible to create a querry to base the combo box on?

thanks in advance

Jay Hallsworth

You can use a union query to normalize your data:
SELECT ItemNumber, Supplier1 as Supplier, 1 as SupNum
FROM tblSageSpreadsheet
UNION ALL

SELECT ItemNumber, Supplier2,2
FROM tblSageSpreadsheet
WHERE Supplier2 is not null
UNION ALL

SELECT ItemNumber, Supplier3, 3
FROM tblSageSpreadsheet
WHERE Supplier3 is not null
UNION ALL
--- etc ---;


--
Duane Hookom
MS Access MVP
--

"jay" wrote in message
...
Hi Duane,

Many thanks for your reply.

If only ... I've been thinking that ever since I've started looking at the
table! ... unfortunately the table isn't mine, it's actually a table I'm
getting from Sage Line100. I'm constantly in bewilderment as to the logic
of Sage's programming!

Regards,

Jay



"Duane Hookom" wrote in message
...
You shouldn't have 5 supplier fields. This isn't normalized. Can you
change your data structures to have a table with fields and records like:

ItemNumber SupplierID
A 1
A 2
A 7
B 1
B 4

This kind of setup allows any number of suppliers per ItemNumber and
makes your combo box a piece of cake.

--
Duane Hookom
MS Access MVP
--

"jay" wrote in message
...
All,

I've got a table as follows:

[ItemNumber] [Supplier1] [Supplier2] [Supplier3] [Supplier4]
[Supplier5]

I need to generate a combo-box listing each of the five suppliers for an
Item Number. I've considered something like

[combo].[additem] dlookup (ItemNumber, Supplier1) - forgive the bad
programming!

but it seems a bit intensive for some low spec workstations

Is it possible to create a querry to base the combo box on?

thanks in advance

Jay Hallsworth

Duane,

Many thanks for your help, mission acomplished ... eventually

I created a make table query from the Sage Line100 ODBC datasource (had to
do this because the union query returned ODBC errors if run directly on the
ODBC datasource) I then created a union query to normalise the table as you
suggested, then created a select query to filter out the ItemNumber I
required and based the combo box row source on that by updating it when
loosing focus on the ItemNumber field on the form.

Still got a bit of work to do writing a macro to re-query the union, but it
should be pretty streight forward from here on in

thanks once again for your help

regards,

Jay




"Duane Hookom" wrote in message
...
You can use a union query to normalize your data:
SELECT ItemNumber, Supplier1 as Supplier, 1 as SupNum
FROM tblSageSpreadsheet
UNION ALL

SELECT ItemNumber, Supplier2,2
FROM tblSageSpreadsheet
WHERE Supplier2 is not null
UNION ALL

SELECT ItemNumber, Supplier3, 3
FROM tblSageSpreadsheet
WHERE Supplier3 is not null
UNION ALL
--- etc ---;


--
Duane Hookom
MS Access MVP
--

"jay" wrote in message
...
Hi Duane,

Many thanks for your reply.

If only ... I've been thinking that ever since I've started looking at
the table! ... unfortunately the table isn't mine, it's actually a table
I'm getting from Sage Line100. I'm constantly in bewilderment as to the
logic of Sage's programming!

Regards,

Jay



"Duane Hookom" wrote in message
...
You shouldn't have 5 supplier fields. This isn't normalized. Can you
change your data structures to have a table with fields and records
like:

ItemNumber SupplierID
A 1
A 2
A 7
B 1
B 4

This kind of setup allows any number of suppliers per ItemNumber and
makes your combo box a piece of cake.

--
Duane Hookom
MS Access MVP
--

"jay" wrote in message
...
All,

I've got a table as follows:

[ItemNumber] [Supplier1] [Supplier2] [Supplier3]
[Supplier4] [Supplier5]

I need to generate a combo-box listing each of the five suppliers for
an Item Number. I've considered something like

[combo].[additem] dlookup (ItemNumber, Supplier1) - forgive the bad
programming!

but it seems a bit intensive for some low spec workstations

Is it possible to create a querry to base the combo box on?

thanks in advance

Jay Hallsworth