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 their 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.

[Peter M]

You will have thousands and thousands of primary keys - that isn't a problem.
It sounds like your data table has the following:
Location
Date
(bunch of other stuff)

If each entry is a sales transaction, then you would want each one to be unique. If you let Access automatically assign a primary key it will create an AutoNumber that is incremented with every data record. It doesn't mean anything, but that's okay. It allows you quickly find that transaction.

If you are using the import wizard to get the database started, you probably want to Index the Location (and allow duplicates) and maybe some other fields that have you are likely to search or filter on (e.g. SalesDate, SalesPerson, SalesType)
Then let Access automatically add a primary key.

[Senor Penguin]

You will have thousands and thousands of primary keys - that isn't a problem.
It sounds like your data table has the following:
Location
Date
(bunch of other stuff)

If each entry is a sales transaction, then you would want each one to be unique. If you let Access automatically assign a primary key it will create an AutoNumber that is incremented with every data record. It doesn't mean anything, but that's okay. It allows you quickly find that transaction.

If you are using the import wizard to get the database started, you probably want to Index the Location (and allow duplicates) and maybe some other fields that have you are likely to search or filter on (e.g. SalesDate, SalesPerson, SalesType)
Then let Access automatically add a primary key.

Ok so you are suggesting just letting Access create a primary key for each line right? My next issue is that I will need to go back and update the information because sometimes our sales information gets rerun and we have to update. How could I set it up so that I can just import the file again and have it replace the amounts for a previous day without creating a new primary key?

You are correct about the table, the main information I need to be able to filter by is the location and date.

Thanks

[Peter M]

That is a bit trickier, but a much better reason to see if you can make a primary key out of the information in the Sale table (that "gets rerun") - what is the key there? If you already have a unique key, use that. (How can the sales information be re-run? What is it based on?)

It sounds like you have multiple systems (something that generates the Sale Excel data, the Sales Excel data, and the Access db). You might need to do this in stages where Step 1 is to import the Sales Data into a temporary table and then have one process to Update existing data and a second process to Append new data.

[Micron]

Are you using a primary key in the correct fashion? I see 3 records for location 1 and they are all different amounts. Then there are 3 more records with the exact same amounts but they are for location 2. In reality then, there is no true duplication of records.

it wont let me upload the table because it says their are duplicates

You should explain this more. This should mean that the target table is incorrectly designed. If the records are legit as I see it, then the location id field cannot be indexed or a PK field.

I would have thousands and thousands of primary keys right?

No, you would have thousands of values in that PK field, but it's only one PK field.

sometimes our sales information gets rerun and we have to update.

Sounds like your table ought to mirror your spreadsheet with the field data types being correctly set. Then link to the spreadsheet. The 1st time, run an append query to upload the records from the linked to the target table. There are other methods, but they will introduce issues around keeping the data types compatible. Then you'd use an append query in the future, but I don't know what to say about duplicates. It seems that you need to allow the location into the target table many times because of there will always be many records for each location. As noted above, if that's the case, this field should not be indexed. After the append query uploads the new records, an update query updates all. It matters not that the records just added would be subject to this update. The problem here is what is the unique (indexed or PK) field?? I don't see where you have one that matters. Would it be OrderID? SalesID? WorkOrderNum? etc. I think your underlying problem is that you have a design problem.

[Senor Penguin]

Are you using a primary key in the correct fashion? I see 3 records for location 1 and they are all different amounts. Then there are 3 more records with the exact same amounts but they are for location 2. In reality then, there is no true duplication of records.

The report was just an example to show structure but what I was trying to show is that each location has a line for each day. I know I am not using the PK correctly as you mentioned I do not have one.

You should explain this more. This should mean that the target table is incorrectly designed. If the records are legit as I see it, then the location id field cannot be indexed or a PK field.No, you would have thousands of values in that PK field, but it's only one PK field.

That's what I meant, the one primary key field would have thousands and thousands of lines, but the bigger concern is updating those records without duplicating them.

Sounds like your table ought to mirror your spreadsheet with the field data types being correctly set. Then link to the spreadsheet. The 1st time, run an append query to upload the records from the linked to the target table. There are other methods, but they will introduce issues around keeping the data types compatible. Then you'd use an append query in the future, but I don't know what to say about duplicates. It seems that you need to allow the location into the target table many times because of there will always be many records for each location. As noted above, if that's the case, this field should not be indexed. After the append query uploads the new records, an update query updates all. It matters not that the records just added would be subject to this update. The problem here is what is the unique (indexed or PK) field?? I don't see where you have one that matters. Would it be OrderID? SalesID? WorkOrderNum? etc. I think your underlying problem is that you have a design problem.

I have no unique identifier, that is my whole problem, which is why I originally tried to use the location as the PK but we have already covered why that wont work. I believe what I am going to do is create another column and combine location+date which will create a unique primary key for each line. Will this help me quickly update previous records?

The main issue is that I am managing daily sales for about 200 locations and because of various connection issues at certain times, I need to go in and rerun reports to make sure they are fully updated. Rather than figuring out which locations and days need updating I just run a MTD report everyday replacing all values. That is why I am so concerned with updating previous values. The only thing I really need to query is location, date, & sales, there is no other information like OrderID or WorkOrder or anything like that. Thanks for the info.

[Senor Penguin]

That is a bit trickier, but a much better reason to see if you can make a primary key out of the information in the Sale table (that "gets rerun") - what is the key there? If you already have a unique key, use that. (How can the sales information be re-run? What is it based on?)

It sounds like you have multiple systems (something that generates the Sale Excel data, the Sales Excel data, and the Access db). You might need to do this in stages where Step 1 is to import the Sales Data into a temporary table and then have one process to Update existing data and a second process to Append new data.

The sales get re-run because there are a lot of locations and we rely on ALL of them not having any technical issues, which of course never happens. So often there are connection issues between the POS systems and the reporting systems, or maybe the manager forgot to do the daily closing which causes missing information in the reports. When the reports get repolled, the numbers change. Uploading historical data wont be an issue but going forward I want to run a VBA to automatically add sales to the Access db daily, even though it's not always 100% accurate the next day, we really need sales updated daily, and then the MTD download corrects any issues from previous days.

I am going to combine the location+date values to create a primary key, I am hoping this will help me quickly update the values, thanks!

[Peter M]

When combing the Location and Date field, I strongly recommend padding the ID field to make sure it is a consistent structure, e.g.:

fld_PrimaryKeyID: string(20-len([fld_Location]),"X") & [fld_Location] & Format([fld_Date],"yyyymmdd")

Where Location is fixed to a maximum of 20 characters. The result would be a primary key that looks like:

XXXXXXXXXXXXXALBERTA20180926
XXXXXXXXXXXXXQUEBEC20180926
XXXXXXXXXXXXXQUEBEC20180925
XXXXXBRITISHCOLUMBIA20180926

[Micron]

combine location+date which will create a unique primary key for each line. Will this help me quickly update previous records

You would know that better than I. If there is no possibility that a location/date combination would be repeated (such as on the same day having to repeat the process that got the info in there in the first place) then I guess so. If that can't happen, then the location/date combo could ensure updates are only applied to the location/date combination you desire.

While there may have been a reason in one or more cases to pad a PK, I've never seen one. It seems like an unnecessary thing to me. The importance of a pk or index is to ensure that the data in one or more fields is not repeated. It should not matter how it's formatted or structured (as in being fixed length). I can only see doing so as being extra work with no payoff.

[Peter M]

The importance of a pk or index is to ensure that the data in one or more fields is not repeated. It should not matter how it's formatted or structured (as in being fixed length). I can only see doing so as being extra work with no payoff.

I've run into problems before when merging fields into a PK, especially with dates or numeric.
For Dates there is the variation in how a date can be submitted. e.g. "Sep 27, 2018" and "9/27/2018" and "20180918" are all the same date but different strings.
With open text fields I've run into a similar problem when merging fields. For example combing UserID and ActiveDevices and accidentally ending up with "Doug" and "Doug2" as the user IDs and then a duplicate PK of "DOUG21" because "Doug" has "21" devices and and "Doug2" only has "1".

[Micron]

IMHO, the field data is NOT to be merged. It should only be either a composite primary key or a composite index. Which seems to depend on one's particular bent. Then there is no need to turn data into one type from another. Sorry, it's just not sensible to me to convert data for the purpose of concatenation - especially dates into strings. Concatenating data only leads to problems, which then requires work-arounds (or worse, redesign) and I think you're proving that point in your last post. Creating a composite key or index would even allow me to replace all date data (or any other field regardless of type) with any other data should the need arise. I could even alter the data type of any of the fields by altering the field properties. The only stipulation would be that the replacement values would have to be unique if I want to maintain a similar index or composite PK outcome. Not trying to be condescending, but I'm thinking that you might want to research composite primary keys and composite indexes if you're not familiar with them. If you are, and you still think your method is a requirement, then I have to think that your approach to creating unique identifiers doesn't achieve the required objective. I say that because first, last or even combined first and last names are poor candidates for unique identifiers.

[Peter M]

IMHO, the field data is NOT to be merged. ... I think you're proving that point in your last post.

Touche - I agree that if I was working solely in Access then a composite primary key would be better.

For SeniorPenguin, that is probably a much better approach than combining fields. Apologies for introducing unnecessary complexity. To make a composite key, Shift-Click both fields and then click the Primary Key icon. You probably also want to set each field to "Index (Duplicates OK)", though it might default to this anyway when it makes the PK.

[Micron]

Do note that this is a composite PK, not a composite index. I have no issue with either but others seem to discourage the use of comp pk's yet have not been able to provide sound reason as to why not.