transfering data from excel...

duckworj

New Member
Joined
Mar 8, 2005
Messages
27
Hi there

I have an excel spreadsheet which I would like to transfer to access. Each row of the spreadsheet represents an enquiry/purchase to a particular item.

I've imported it (after sorting out all the column data) and it works ok..


However, I have duplicate information relating to orders (where one purchaser has maybe asked about a few items)

I really need to seperate the columns into different tables..

ie 1 customer table
1 item table
1 billing information table
1 results table

But each table would need to use data from one of the other tables...
for example I would need to have the customer details and item details for the billing table..

Obviously I wouldn't want to have 2 customer records if they were a frequent user...

What would be the best way to handle this..and how would I do it...(I'm more used to excel than access)

Cheers

DUCKERS..
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
What you need to do is create a query that shows all the distinct customers.

Then use that in a make table to create a customer table.

You could do something similar with the item table.

You would add a unique primary key to both of these tables, that's normally an Autonumber field.

For each bill you would have records in the billing information table that included the primary keys from the other 2 tables, and any other information.

All of this can be done with append/update/make-table queries.

But it's hard to advise on the exact queries without knowing the current data structure.

Try searching on the forum or web for information on Normalizing a database.
 

duckworj

New Member
Joined
Mar 8, 2005
Messages
27
Hi there

Thanks for that....I am pretty new to access (thats why I am using excel spreadsheets to store my data)..can we take it step by step...

I have a little knowledge of making queries...but am not sure how I would filter out the duplicate entries...


Can you advise me on how to do that first...I would still need to link the other information to the original student...Would I not lose data?

Thanks any help would be great to boost my confidence.

Duckers.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
To just return the unique records you could either use View>Totals in the query.

Or set the properties of the query to only return unique records.

As to linking the information, that's the hard part really and without knowing more about the data I can't really recommend anything of the top of my head.

Did you try a search as I suggested?

I'm sure there's plenty of articles out there that are more comprehensive.
 

duckworj

New Member
Joined
Mar 8, 2005
Messages
27
Hi there

Thanks for that...

I'll have a go

I didn't search anywhere cos sometimes these sites are way too technical...(Not this one though!)

Will probably be asking more questions

Jane
 

duckworj

New Member
Joined
Mar 8, 2005
Messages
27
The data at the moment is stored on a few spreadsheets...

one large spreadsheet..about 5000 rows long x say 30 columns wide Each column is a piece of info ie forename surname item no etc...This is the main database which shows all about where a customer is up to.

I then have another spreadsheet which liists all the products, availability and cost...

and another spreadsheet which i use to generate invoices (sometimes we bill to a third party on behalf of customer)

I would like to put the 3 together in one access database, to end the duplication.


Hope this helps
 

Watch MrExcel Video

Forum statistics

Threads
1,118,452
Messages
5,572,209
Members
412,448
Latest member
ManuW
Top