"pivoting" data with a querry

J

jay

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
 
D

Duane Hookom

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.
 
J

jay

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Duane Hookom

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

jay

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 said:
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 said:
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 said:
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
--

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
Click to expand...
Click to expand...
Click to expand...
 
Top