Help with converting a table and making a report.

[Graheme]

Hi, couple of questions from a newbie:

First, I have a database with basically 3 tables:
Customer, which has cust#, name and address;
Items which has cust#, type, serial# and description;
Items can have duplicate records.
Orders which is not linked to either.
Orders has customer name, address and then 8x3 items fields: item 1
type, item 1 serial# and item 1 description... to item 8 description.

Being the most junior I have no control over the schema, or anything
else :-(.

I need to take the records from orders and items and put them into
orders. If there is more than 8 items, it makes another orders record
and the first item description will be 'order continued'. It's an access
database so I figure I'll be using VBscript or something. What's the
easiest way to do it?

Second problem.
I've got to make a report with 3 columns.
Item type can be either 'A' or 'B'.
The columns are:
Type 'A' | Serial# | Type 'B'

I've got to list the total type 'A' matching the serial #, then the
total of type 'B'.
Is there any easy way to do this?

I'm a Java programmer at heart, so all this is rather new and of course
I ave a very tight deadline :-)

many thanks,
Graheme.

[Tom Wickerath]

Hi Graheme,

Your Orders table is not normalized! You really do *NOT* want a structure that includes
item 1 type, item 1 serial# and item 1 description... to item 8 description.

Check out the short paper on database design by Mike Hernandez, available at:
http://www.datatexcg.com/DatabaseDes...DesignInfo.htm

Another paper, more in-depth, by the same author titled "Understanding Normalization":
http://www.eade.com/AccessSIG/downloads.htm

Also
http://support.microsoft.com/default...b;en-us;234208

You should redesign before attempting to tackle your second problem.

_________________________________________

"Graheme" <sp**@trap.com> wrote in message
news:MP************************@news.syd.swiftdsl. com.au...

Hi, couple of questions from a newbie:

First, I have a database with basically 3 tables:
Customer, which has cust#, name and address;
Items which has cust#, type, serial# and description;
Items can have duplicate records.
Orders which is not linked to either.
Orders has customer name, address and then 8x3 items fields: item 1
type, item 1 serial# and item 1 description... to item 8 description.

Being the most junior I have no control over the schema, or anything
else :-(.

I need to take the records from orders and items and put them into
orders. If there is more than 8 items, it makes another orders record
and the first item description will be 'order continued'. It's an access
database so I figure I'll be using VBscript or something. What's the
easiest way to do it?

Second problem.
I've got to make a report with 3 columns.
Item type can be either 'A' or 'B'.
The columns are:
Type 'A' | Serial# | Type 'B'

I've got to list the total type 'A' matching the serial #, then the
total of type 'B'.
Is there any easy way to do this?

I'm a Java programmer at heart, so all this is rather new and of course
I ave a very tight deadline :-)

many thanks,
Graheme.