Importing from Excel (1 Viewer)

LizJohnson · Jun 14, 2018

I am going to be importing data from an Excel file into Access on a daily basis.

The programmer of the information that is being sent to me on the Excel spreadsheet is sending "all" the information every day. What this means is that I will be getting duplicate information every day, along with updated information.

Some of the information I will be getting is payment information. The accounts will be able to make 3 payments for 1 bill. I have a field in Access for each payment, but of course, in one record.

How can I import this information from Excel without duplication of the record set, but still updating the additional information I may get for each record (Add each payment when they pay)?

GohDiamond · Jun 14, 2018

Does each IMPORTED record have a unique transaction ID?

June7 · Jun 14, 2018

Would probably be easier if each payment were a separate record in a related table

Is the sheet simple enough to set link? The sheet includes new bills as well as payments?

LizJohnson · Jun 14, 2018

I wish!!! I have been working with this database for ever! (At least it seems that way). I have been working on it for over a month. Originally couldn't get it to populate the query or forms correctly. I was going to put the payments on a separate table and have the payments post separately. This basically was rejected because of how the payments have to be grouped together to show a total payment by customer but they can have different tobacco types.

I am also having problems with my Macros. I have set up the macro to change the headers to match my Access fields. I saved the Macro's to a separate worksheet so that I can just open that worksheet to run the macro. When I run the macro on the next day's file, I always get a run error. When I posted this problem on the excel forum in this forum, the person told me to link the excel sheet. The problem with this and that it doesn't really load the data so the data won't transfer into my queries and forms. That, then makes linking the excel sheet useless. Also, I did test this theory, but you have to have the spreadsheet open in order to view the data. Well, this is going to be used on many computers, so that also won't work.

I going to change my middle name to frustrated.

June7 · Jun 14, 2018

I do not understand "doesn't really load the data" nor why the sheet has to be open. I link to simple Excel sheets without issue. Then I view data same as a table in Access.

Pat Hartman · Jun 14, 2018

This basically was rejected because of how the payments have to be grouped together to show a total payment by customer but they can have different tobacco types.

YOU are the designer and YOU are responsible for storing data efficiently so that YOU can create the necessary reports and forms for the user. Do NOT be bullied into storing the data incorrectly simply because you don't understand how you can make the reports that are needed. In a spreadsheet, the presentation and data layers are merged. What you see is what you get. That is not the case with an application build on a relational database. Data should be stored in a normalized fashion and you use reports and forms to interact with the user. The user NEVER, EVER sees tables or queries so he has no idea regarding how the data is actually stored.

Bottom line = each payment should be a separate row. What if next week someone decides to allow 4 payments. Everything you have done to incorrectly fix the number of payments to three will need to be modified. That will not happen if the payments are stored as one per row. Remember, in the relational world when you have more than one of something, you have many and many ALWAYS requires a separate table so each "many" gets stored as a separate row. There are three relationships between tables 1-m, 1-1, m-m. Notice that 1-3 isn't a valid relationship.

LizJohnson · Jun 15, 2018

Ok, even if I can get the payments to work where they will sum based on the main number on a separate table, this is still not answering my initial question. Is it possible to upload the information from the excel spreadsheet without duplicating the data. There is other data that is uploaded besides the payments that is also just as vital that cannot be duplicated.

isladogs · Jun 15, 2018

I've only skim read the thread so apologies if I'm repeating previous comments or missing something.

As long as your Excel data has a unique field which is used in the import, you can add new records using an 'unmatched' append query. This will only add records where the unique field is null in the destination table is. it doesn't exist.
Existing records would be updated using an update query.
So you would create a procedure and run both of these in turn.

OR if you are sure you get all the data every day, empty the destination table then append all the new records. If you do that, an autonumber ID field will obviously change daily so you ma need to plan a work round for that

LizJohnson · Jun 15, 2018

Thanks for the info. I'll have to look into this further. The first table I update does have a unique identifier and it is only adding the new records that are not there, but I am getting additional email addresses for this table that is not uploading that I would like to have in the table.

The other two tables do not have unique identifiers due to the relationship of the numbers they are tied to. Sounds like I will have to create some type of procedure to amend these numbers.

isladogs · Jun 15, 2018

If you have multiple email addresses for the same client/company, these should be stored in a separate table linked in a one to many relationship.
That is unless you are using the dreaded multivalue field to which I'd say DON'T.

Pat Hartman · Jun 15, 2018

Unique identifiers are required to eliminate the possibility of importing duplicates. In addition to the autonumber primary key of your payment table, you may need to create a compound unique index that includes several fields including the datepaid and amountpaid. Of course, that is in itself problematic since it prevents the possibility of making two payments of the same amount on the same day but since that is not likely, it may not actually be a problem.

LizJohnson · Jun 20, 2018

So I set up a payment table, like suggested. I have now had to adjust my query to pull from the payment table instead of the original table where I had my payments coming from. Now when I look at the numbers in my query, instead of it showing just one time, it is showing twice and sometime 4 times for the same item, even though it is only once on my table.

Any idea's why this is happening in my query? I did set the relationship to one-to-many.

isladogs · Jun 20, 2018

LizJohnson said:
So I set up a payment table, like suggested. I have now had to adjust my query to pull from the payment table instead of the original table where I had my payments coming from. Now when I look at the numbers in my query, instead of it showing just one time, it is showing twice and sometime 4 times for the same item, even though it is only once on my table.

Any idea's why this is happening in my query? I did set the relationship to one-to-many.

Without being able to see your query design or results, suggest you set Unique Values =Yes which is the same as using SELECT DISTINCT in query SQL.

LizJohnson · Jun 20, 2018

I just did that. I did eliminate a lot of them but still kept 8 duplicate records. So, for example, I had one account that should be showing 2 times because I have two different tobacco license #'s but the same sales tax number. It was originally showing 8 times (repeating the info 4 times) but after I set "YES" to the Unique identifier, it is now only showing 4 (repeating 2 times). I'm using this query in a form and when I looked at the form, it still showed all 8 records instead of the now just 4 records? So, even though the query is getting better, where I really want to see the information (the form) is not showing me what is on the query.

Still need help, please.

isladogs · Jun 20, 2018

Then you'll need to post the query SQL and some example results that show the issue.
Also explain what the query is intended to show.

LizJohnson · Jun 20, 2018

Here is my SQL:
SQL
SELECT DISTINCT tblCigaretteDataCollection.SalesandUseTaxNumber,
tblCigaretteDataCollection.TobaccoLicenseNumber, tblCigaretteDataCollection.NumberofLocations,
tblCigaretteDataCollection.[Sells Cigarettes], tblCigaretteDataCollection.Stamper,
tblCigaretteDataCollection.Amended, tblCigaretteDataCollection.Packof20s, [Packof20s]*[TaxRateon20s] AS
TaxDuePacks20s, tblCigaretteDataCollection.Packof25s, [Packof25s]*[TaxRateon25s] AS TaxDuePacks25s,
tblCigaretteDataCollection.Rollof20s, [Rollof20s]*30000 AS TotalRollof20s, [Rollof20s]*30000*[TaxRateon20s]
AS TaxDueonRollof20s, tblCigaretteDataCollection.[Partial20s-Rolls], [Partial20s-Rolls]*[TaxRateon20s] AS
TaxDueonPartial20sRolls, tblCigaretteDataCollection.Padsof20s, [Padsof20s]*1500 AS TotalPadsof20s,
[Padsof20s]*1500*[TaxRateon20s] AS TaxDueonPadsof20s, tblCigaretteDataCollection.[Partial20s-Pads],
[Partial20s-Pads]*[TaxRateon20s] AS TaxDueonPartial20sPads, tblCigaretteDataCollection.Padsof25s,
[Padsof25s]*1500 AS TotalPadsof25s, [Padsof25s]*1500*[TaxRateon25s] AS TaxDueonPadsof25s,
tblCigaretteDataCollection.[Partial25s-Pads], [Partial25s-Pads]*[TaxRateon25s] AS TaxDueonPartial25sPads,
tblCigaretteDataCollection.TaxRateon25s,
Round((Nz([TaxDuePacks20s],0)+Nz([TaxDuePacks25s],0)+Nz([TaxDueonRollof20s],0)+Nz([TaxDueonPartial20s
Rolls],0)+Nz([TaxDueonPadsof20s],0)+Nz([TaxDueonPartial20sPads],0)+Nz([TaxDueonPadsof25s],0)+Nz([TaxDu
eonPartial25sPads],0)),2) AS [TotalFloorTax Due], qryPayment.[Payment 1], qryPayment.[Payment 2],
qryPayment.[Payment 3], Round((Nz([TotalFloorTax Due],0)-Nz([Payment 1],0)-Nz([Payment 2],0)-Nz([Payment
3],0)),2) AS [Remaining Balance]
FROM tblCigaretteDataCollection INNER JOIN qryPayment ON
tblCigaretteDataCollection.SalesandUseTaxNumber = qryPayment.SalesandUseTaxNumber;

Here is also an example of what it looks like:
qry73A421SalesandUseTaxNumberTobaccoLicenseNumberNumberofLocationsSells CigarettesStamperAmendedPackof20sTaxDuePacks20sPackof25sTaxDuePacks25sRollof20sTotalRollof20sTaxDueonRollof20sPartial20s-RollsTaxDueonPartial20sRollsPadsof20sTotalPadsof20sTaxDueonPadsof20sPartial20s-PadsTaxDueonPartial20sPadsPadsof25sTotalPadsof25sTaxDueonPadsof25sPartial25s-PadsTaxDueonPartial25sPadsTaxRateon25sTotalFloorTax DuePayment 1Payment 2Payment 3Remaining Balance063354200000120YesNoNo500$250.00300$187.50390000$45,000.00400$200.0034500$2,250.001000$500.0011500$937.50400$250.000.625$49,575.00$6,868.75$6,868.75$6,868.75$28,968.75063354200000120YesNoNo500$250.00300$187.50390000$45,000.00400$200.0034500$2,250.001000$500.0011500$937.50400$250.000.625$49,575.00$16,525.00$21,000.00$12,050.00$0.0006335420000020YesNoNo150$75.00150$93.75130000$15,000.00

11500$750.00

57500$4,687.50

0.625$20,606.25$6,868.75$6,868.75$6,868.75$0.0006335420000020YesNoNo150$75.00150$93.75130000$15,000.00

11500$750.00

57500$4,687.50

0.625$20,606.25$16,525.00$21,000.00$12,050.00($28,968.75)

For this group, there should only be 1 of each TobaccoLicenseNumber so I should only have 2 lines for the SalesandUseTaxNumber.

I need to have the payments on this query so that I can use this information to calculate the remaining balance due from the Total tax due.

I hope this helps.

LizJohnson · Jun 20, 2018

The example doesn't look anything like what it did in my message. Sorry.

isladogs · Jun 20, 2018

Sorry but it's not really readable.
Use code tags (# button above reply box) to preserve indentation and layout as the original code. Pasting a screenshot of results would be better

LizJohnson · Jun 20, 2018

Code:

[FONT=Calibri][COLOR=#000000][B]qry73A421[/B][/COLOR][/FONT][FONT=Calibri][COLOR=#000000]SalesandUseTaxNumber[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TobaccoLicenseNumber[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]NumberofLocations[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Sells Cigarettes[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Stamper[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Amended[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Packof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDuePacks20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Packof25s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDuePacks25s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Rollof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TotalRollof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDueonRollof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Partial20s-Rolls[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDueonPartial20sRolls[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Padsof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TotalPadsof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDueonPadsof20s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Partial20s-Pads[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDueonPartial20sPads[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Padsof25s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TotalPadsof25s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDueonPadsof25s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Partial25s-Pads[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxDueonPartial25sPads[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TaxRateon25s[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]TotalFloorTax Due[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Payment 1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Payment 2[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Payment 3[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Remaining Balance[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]063354[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]2000001[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]20[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Yes[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$250.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]300[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$187.50[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]3[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]90000[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$45,000.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]400[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$200.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]3[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]4500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$2,250.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1000[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$500.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$937.50[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]400[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$250.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]0.625[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$49,575.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$6,868.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$6,868.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$6,868.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$28,968.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]063354[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]2000001[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]20[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Yes[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$250.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]300[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$187.50[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]3[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]90000[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$45,000.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]400[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$200.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]3[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]4500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$2,250.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1000[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$500.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$937.50[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]400[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$250.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]0.625[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$49,575.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$16,525.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$21,000.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$12,050.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$0.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]063354[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]2000002[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]0[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Yes[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]150[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$75.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]150[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$93.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]30000[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$15,000.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$750.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]5[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]7500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$4,687.50[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]0.625[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$20,606.25[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$6,868.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$6,868.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$6,868.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$0.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]063354[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]2000002[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]0[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Yes[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]No[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]150[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$75.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]150[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$93.75[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]30000[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$15,000.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]1500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$750.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]5[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]7500[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$4,687.50[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]0.625[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$20,606.25[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$16,525.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$21,000.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]$12,050.00[/COLOR][/FONT][FONT=Calibri][COLOR=#000000]($28,968.75)[/COLOR][/FONT]

LizJohnson · Jun 20, 2018

That didn't work either. I used the # code. I also copied/pasted.

I've attached a word doc that has a print screen of what I'm talking about. I hope this helps.

I've had to zip the attachment.

Importing from Excel (1 Viewer)

Registered User.

"Access- Imagineer that!"

AWF VIP

Registered User.

AWF VIP

Super Moderator

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Super Moderator

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

Registered User.

Attachments

Similar threads

Users who are viewing this thread