sales data

[Herman]

I have imported sales data into an Access database with
the information looking like this:
cust1,sale1
cust1, sale2
cust2,sale1
cust2,sale2
cust2,sale3
etc.

What I would like to do is break the data into 2 tables
and link via a primary key. I would end up with 2 tables

first:
key1,cust1
key2,cust2
key3,cust3

second:
key1, sale1
key1, sale2
key2,sale1
key2,sale2
key2,sale3

Thanks

 

[Roxie Aho]

-----Original Message-----
I have imported sales data into an Access database with
the information looking like this:
cust1,sale1
cust1, sale2
cust2,sale1
cust2,sale2
cust2,sale3
etc.

What I would like to do is break the data into 2 tables
and link via a primary key. I would end up with 2 tables

first:
key1,cust1
key2,cust2
key3,cust3

second:
key1, sale1
key1, sale2
key2,sale1
key2,sale2
key2,sale3

Thanks
.

The idea behind a relational database such as Access is to
not duplicate information. You're on the right track to
divide your information into more than one table. I would
suggest:

tblCustomers
CustomerID (primary key, indexed, no duplicates)
CustomerName
Other relevant fields such as Address, City, State etc.

tblSales
SalesID (primary key, indexed, no duplicates)
CustomerID (foreign key from tblCustomers)
Sale
Other relevant fields

For simplicity, let's assume you have two customers and
make three sales to the first customer and one sale to the
second customer. Your tables would look something like
this.

tblCustomers
CustomerID 1 CustomerName ABC Company
CustomerID 2 CustomerName XYZ Company

tblSales
SalesID 1 CustomerID 1 Sale Hamburgers
SalesID 2 CustomerID 2 Sale Hamburgers
SalesID 3 CustomerID 1 Sale Hot Dogs
SalesID 4 CustomerID 1 Sale Hamburgers

Hope this helps you get started.

Roxie Aho
roxiea at usinternet.com

 

[John Vinson]

I have imported sales data into an Access database with
the information looking like this:
cust1,sale1
cust1, sale2
cust2,sale1
cust2,sale2
cust2,sale3
etc.

What I would like to do is break the data into 2 tables
and link via a primary key. I would end up with 2 tables

first:
key1,cust1
key2,cust2
key3,cust3

second:
key1, sale1
key1, sale2
key2,sale1
key2,sale2
key2,sale3

The first table can be extracted using a MakeTable query or (probably
better since you have more control) create your target tables first
and use an Append query. For instance, you could create table [First]
with two fields - an Autonumber key, and a Customer field. Create a
Query based on the imported table, selecting only the Cust field, and
append it to the Customer field of first.

Now create a second query joining [first] to the import table, joining
*by the Customer field*. (This does rely on the assumption that no two
customers happen to have the same name). Select the Key field from
[First] and the [Sale] field from the import table, and append this to
[Second].

 

[Herman]

John,

Thank you.

Herman

-----Original Message-----

I have imported sales data into an Access database with
the information looking like this:
cust1,sale1
cust1, sale2
cust2,sale1
cust2,sale2
cust2,sale3
etc.

What I would like to do is break the data into 2 tables
and link via a primary key. I would end up with 2 tables

first:
key1,cust1
key2,cust2
key3,cust3

second:
key1, sale1
key1, sale2
key2,sale1
key2,sale2
key2,sale3

The first table can be extracted using a MakeTable query or (probably
better since you have more control) create your target tables first
and use an Append query. For instance, you could create table [First]
with two fields - an Autonumber key, and a Customer field. Create a
Query based on the imported table, selecting only the Cust field, and
append it to the Customer field of first.

Now create a second query joining [first] to the import table, joining
*by the Customer field*. (This does rely on the assumption that no two
customers happen to have the same name). Select the Key field from
[First] and the [Sale] field from the import table, and append this to
[Second].


.