Question How to Upload Excel File with Duplicate Values

[Senor Penguin]

Hey Guys, I am a complete newb in Access so please go easy on me. I am trying to create a sales Database by importing an Excel sheet but it gets a little bit tricky because the report I want to upload is for many locations and many days so a lot of the values are duplicated.

I was trying to use the location number as the primary key but it wont let me upload the table because it says there are duplicates. Obviously I am not doing this correctly, is my only option to have Access create a primary key for each new line? I feel like this would not be correct because then I would have thousands and thousands of primary keys right?

I guess I am trying to figure out what is the best way to structure the design of this as I have not been able to find examples for this same scenario with multiple locations.

This is how my report is structured in Excel, but a lot more locations and dates. I just want to be able to quickly query this information.

 

[plog]

Detailed explanation:

https://en.wikipedia.org/wiki/Primary_key


Short version:

A primary key is data that can be used to uniquely identifies a row of data.

If you need a primary key, letting Access add one for you is never a bad idea. With what you have posted, I see no natural primary key in the data itself.

 

[Senor Penguin]

Detailed explanation:

Short version:

A primary key is data that can be used to uniquely identifies a row of data.

If you need a primary key, letting Access add one for you is never a bad idea. With what you have posted, I see no natural primary key in the data itself.

Thanks, That's what I thought but my concern is that I will need to go back and update some of this information. Our Sales numbers may change and need to be re-updated for a previous day, so how could I re-import an Excel file and have it replace the previous days sales rather than just create a new line?

 

[plog]

if you are replacing the prior days sale, you can delete all the records in the database with that date, then import the updated data.

 

[isladogs]

First import to a temporary table or better still link to the Excel file.
Use an UPDATE query to update existing records in main table or an APPEND query to add new records

 

[Senor Penguin]

if you are replacing the prior days sale, you can delete all the records in the database with that date, then import the updated data.

Ok that makes sense but one last thing. I have to manage too many locations, sales for about 200 locations everyday so rather than figuring out what locations and days need to be updated I run a MTD report everyday and replace all sales everyday, would you suggest basically just deleting all the data for say, September, and just re-importing it daily? I plan to automate this all through VBA as I have experience there, I just want to make sure I know what I am doing manually before I get into that part.

 

[Senor Penguin]

First import to a temporary table or better still link to the Excel file.
Use an UPDATE query to update existing records in main table or an APPEND query to add new records

:banghead: I need to watch more Youtube videos, this gives me something to research though, Thanks

 

[Pat Hartman]

We don't know what data the input file contains so we can't give you a specific answer.

When the spreadsheet is the "master", then deleting all the data from the Access table and re-importing from Excel will keep you up to date.

If the spreadsheet only contains "current" data plus records that update previously imported rows, then the problem is more difficult. You would import the entire file into a temp table and then append to the Access table to add the new records. A second query would join to the temp table and run an update query and that would update any old records.

Tell us more about what the Excel file contains and we can be more specific.

 

[arnelgp]

if the worksheet contains the gross for a day, you can combine location+date columns as your primary key.

 

[Senor Penguin]

if the worksheet contains the gross for a day, you can combine location+date columns as your primary key.

This is what I was thinking! Yes there is only 1 total sales per day, per location, so I could add a column combing these and each of these values would be unique. Would this allow me to easily import the table again and just set it to allow overwrite of duplicate primary keys? Or would I still need to go through the process of deleting old records and re-importing?

 

[arnelgp]

yes this will work for your case. there is no need to delete the dups they will be ignored.

 

[Cronk]

That takes care of new records.

However in #3

I will need to go back and update some of this information

I won't but in on Arnel, leaving him to give you the process of updating amended data.

 

[Senor Penguin]

yes this will work for your case. there is no need to delete the dups they will be ignored.

Ok so it wont create duplicate entries with this but can I also make it overwrite existing entries or can they only be ignored?

Thanks

 

[isladogs]

What I wrote in post 5 still applies using a composite PK field.

 

[Pat Hartman]

I wrote something similar with more detail in #8.