Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2018, 07:14 AM   #16
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Importing from Excel

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 is offline   Reply With Quote
Old 06-20-2018, 07:16 AM   #17
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Importing from Excel

The example doesn't look anything like what it did in my message. Sorry.
LizJohnson is offline   Reply With Quote
Old 06-20-2018, 07:43 AM   #18
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Importing from Excel

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-20-2018, 07:46 AM   #19
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Importing from Excel

Code:
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)
LizJohnson is offline   Reply With Quote
Old 06-20-2018, 07:53 AM   #20
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Importing from Excel

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.
Attached Files
File Type: zip print screen query 73A421.zip (140.1 KB, 9 views)
LizJohnson is offline   Reply With Quote
Old 06-20-2018, 08:37 AM   #21
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Importing from Excel

If you remember I said to use code tags for the code ONLY and a screenshot of the query results as e.g. png file

Anyway I've looked at the Word doc of the screenshot but it doesn't really help.
I can see that the 4 highlighted records appear to be 2 pairs of duplicates.
However, as the query contains lots of fields, not all are visible so I can't tell if you really are getting duplicates

As I can't read the query SQL either, we're no further forward
Have you tried using the duplicates query wizard?

If you can't solve this yourself, save a stripped down copy of your database with just the items needed to see the problem. You can probably also remove most of the records as long as some 'dupes' are included.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-20-2018, 09:15 AM   #22
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Importing from Excel

If you repeat the account number then of course each associated payment will join to each account record if you are using the account number as the linking key. Now i see you are linking on tax number so not clear how data is structured. Provide db for analysis.


Last edited by June7; 06-20-2018 at 09:21 AM.
June7 is offline   Reply With Quote
Old 06-21-2018, 06:46 AM   #23
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Importing from Excel

I have finally been able to get a stripped down version of my database and have attached that. There are some forms that are there that I'm not using, but I don't want to delete those yet because they may be attached to something. I've tried so many things, I forgot what I've done on some things. LOL.

I have left some payments on that have the same sales & use tax number but different tobacco tax numbers. This is what is going to happen. This is where the duplication, I believe, is happening.

All of the information that I have on this database is only entered once.

Please let me know what you think and how to resolve having duplicate showing up on my query 73A421 and also then on my form 73A421W.
Attached Files
File Type: zip Floor Stock 2018 test 1.zip (244.5 KB, 4 views)

LizJohnson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Importing from Excel tjtross Tables 4 04-22-2015 04:05 AM
Importing from Excel Armine Excel 11 08-27-2013 04:17 AM
Excel importing fun! Smee Tables 11 03-05-2007 12:24 PM
Importing from Excel ttutela General 3 04-15-2005 06:04 AM
Importing From Excel yessir Tables 1 05-11-2004 07:45 AM




All times are GMT -8. The time now is 04:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World