transfering data from excel...


New Member
Mar 8, 2005
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)



Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
Upvote 0
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.

Upvote 0
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.
Upvote 0
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

Upvote 0
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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back