Help with coding to eliminate multiple queries!

[Haggrr]

Please don't over think this one. Assume that I understand Access fairly well on a 'above' basic level. I am able to achieve the results using 'multiple' queries, but I also know there is simpler code that can achieve the same results faster. Below represents the main 'data' table. It contains hundreds of thousands of records all identify 'mainly' by the [Job Number].



I have multiple [My Contractor]s. I have multiple [My Customers] They are all in Tables dedicated to each class, and are referenced as to not allow billing by, or to, any 'random' person or company.

Here's the Question: What I'm trying to achieve is much faster query (or whatever, maybe code in fields that call for lists to refresh) that, when ran, will tell me IF [My Contractor] has billed me, but I DIDN'T bill one of [My Customer] IF so THEN show me the LAST record of that [Job Number] based on largest [File Number] related to that [Job Number]

(I'm looking to check to see that I didn't miss billing my customers for jobs that are billed to me by my contractors) I have a series of queries that do the job, but are way too slow to add into forms for instant results.

If [My Contractor] billed me, but I didn't bill [Amonut] any of [My Customer] then show last record based on largest [File Number] to query [abc] that is the source of
[list 123]

Job Number	SetterCompany	Date To	Date From	Amount	File Number	Text	Code
834852	My Contractor	11/18/14 4:42:43 PM			2568148
834852	Mr Contractor		11/19/14 4:43:30 PM	$6.76	2568161		10
834852	My Company	11/19/14 4:46:12 PM			2568176
834852	My Customer	11/20/14 10:28:22 AM		$8.50	2568216

[June7]

How do we know which values in SetterCompany are contractor or customer?

Post the working queries for analysis.

What do these records represent? Why is the Amount field empty on some?

[ranman256]

What if you do it in pieces to keep things in mental order...

qsContractorBilled
select * from tbl where [settercompany]='My Contractor' and [amount] >0

and

qsCustNotBilled
select * from tbl where [settercompany]='My Customer' and [amount] =0

then an outer join on the 2 to reveal your need
SELECT qsContractorBilled.ClientID, qsCustNotBilled.ClientID, qsCustNotBilled.Nights
FROM qsCustNotBilled RIGHT JOIN qsContractorBilled ON qsCustNotBilled.ClientID = qsContractorBilled.ClientID
WHERE (((qsCustNotBilled.ClientID) Is Null));

(or similar)

[Haggrr]

Job Number	SetterCompany	Date To	Date From	Amount	File Number	Text	Code
834852	Jim Johnston	11/18/14 4:42:43 PM			2568148
834852	Jim Johnston		11/19/14 4:43:30 PM	$6.76	2568161		10
834852	Control	11/19/14 4:46:12 PM			2568176
834852	Tom's Jewelers	11/20/14 10:28:22 AM		$8.50	2568216

On 11/18/14 at 4:42:43 PM I scanned a job #834852 [Job Number] to a contractor of mine named Jim Johnson. [Setter/Company]

On 11/19/14 at 4:43:30PM Jim Johnson [Setter/Company] scanned the job # 834852 [Job Number] back to me and billed me $6.76 [Amount]

On 11/19/14 at 4:46:12 PM Jim Johnson scanned that same job to Control [Setter/Company] for me to inspect. If I needed to know where that job is at any give moment I enter the [Job Number] and 'at that moment in time' is would see it was sitting in the Control area.

On 11/20/14 at 10:28:22 Am I billed my customer, Tom's Jewelry [Setter/Company]

I just need a code (probably in data entry field that refreshs the lists on my forms) that will check and see which jobs I haven't billed to any of my customers. Something that would be the source for a list that keeps me abreast of what jobs might have been given to my customers without billing. The reason it is possible to accidentally does that is the volume of work done and the many years of trust.

Here are the SQL I now use! It tells me if I didn't bill and if I didn't bill more than my contractors billed me!

SELECT [Compare All Setters Totals].[Job Number], [Compare All QG Totals2].MaxOfSetterCompany, [Compare All QG Totals2].SumOfAmount, [Compare All Setters Totals].SumOfAmount, [Compare All Setters Totals].LastOfSetterCompany, [Compare All Setters Totals].[LastOfDate From]
FROM [Compare All Setters Totals] LEFT JOIN [Compare All QG Totals2] ON [Compare All Setters Totals].[Job Number] = [Compare All QG Totals2].[Job Number]
WHERE ((([Compare All QG Totals2].SumOfAmount) Is Null Or ([Compare All QG Totals2].SumOfAmount)<([Compare All Setters Totals].[SumOfAmount])) AND (([Compare All Setters Totals].SumOfAmount)>0) AND (([Compare All Setters Totals].[LastOfDate From]) Is Not Null And ([Compare All Setters Totals].[LastOfDate From])>=#5/1/2009#))
ORDER BY [Compare All Setters Totals].[LastOfDate From];

SELECT [Compare All Setters].[Job Number], Last([Compare All Setters].SetterCompany) AS LastOfSetterCompany, Sum([Compare All Setters].Amount) AS SumOfAmount, Last([Compare All Setters].[Date From]) AS [LastOfDate From], [Compare All Setters].Text
FROM [Compare All Setters]
GROUP BY [Compare All Setters].[Job Number], [Compare All Setters].Text;

SELECT [Compare All QG2].[Job Number], Max([Compare All QG2].SetterCompany) AS MaxOfSetterCompany, Sum([Compare All QG2].Amount) AS SumOfAmount, Last([Compare All QG2].[Date To]) AS [LastOfDate To]
FROM [Compare All QG2]
GROUP BY [Compare All QG2].[Job Number];

[Haggrr]

I think you understand what I'm wanting. However there are many 'my contractors' and many 'my customers'. The [job number] is the key (not unique )

Here's a more accurate look at my main table.

Job Number	SetterCompany	Date To	Date From	Amount	File Number	Text	Code
834852	Jim Johnson	11/18/14 4:42:43 PM			2568148
834852	Jim Johnson		11/19/14 4:43:30 PM	$6.76	2568161		10
834852	Control	11/19/14 4:46:12 PM			2568176
834852	Tom's Jewelry	11/20/14 10:28:22 AM		$8.50	2568216

I scanned Jim that job on 11/18, he did the work, scanned the job back to me the next day and billed me $6.76 [amount] (I have my contractors bill through my program as to be able run checks and balances without having to re-enter their data). He then scanned that job to 'Control', an area of the shop so I know where that job is at all times. Then I billed that job to my customer on 11/20/14 for $8.50 [Amount]

I only need to be able to easily verify that if a was charge 'any amount' that I charged 'some company' something greater than what I was charged.

Don't read too much into my program.

[orange]

From your sample data it seems for each JobNumber you must have at least 2 amounts
One amount(amountA) with a DateFrom as a valid Date, the other amount(amountB) must have a DateTo with a valid Date.
Also, the DateTo must be > than DateFrom AND AmountB must be > AmountA
This represents the Ideal set up. You can play off of that logic to identify other situations.

You asked us not to read too much into this, so i won't.
But, usually you separate your AmountsIN and AmountsOUT.
If it's critical to your business you may have identified transactions where specific units of work make up a transaction.
You can compare transactions to determine balances; outstanding amounts....
You must have some way to identify Customer, Contractor and Company.

Anyway, good luck.

[Haggrr]

If only I knew 10 years then what I know today...


I need to group my main [table] by [Job Number]. Look within those groups for records where [Date From] and [Amount] is not null, and records with that same [Job Number] where [Date To] is either null or not null, but [Amount] is null.

[June7]

Consider:

SELECT * FROM tablename WHERE JobNumber IN (SELECT JobNumber FROM tablename WHERE Amount Is Null);

[orange]

Perhaps you could tell us more about the database. Did you create it? Do you use this for a business? Is this used in calculating taxes, wages...?

You really start these projects with a clear statement of your business. Then identify the entities (things) involved and the attributes you want to record about these "things" and build a model. Test the model with some "test data" representative of your business. Does the model support the business? If not, is it the model or the data? Determine the issue and resolve it by adjusting the model or the data. Repeat this till the model matches your needs. Then develop the database.

You may be able to normalize your data and recreate a database to suit your needs. Or you can continue with what you have, which will probably require a lot of "workarounds" to get what you need. Many people get the software, then create a "database". Then after confronting a few brickwalls, start to look at some of the underlying relational database principles and theory, then take a new approach to database development.

Here's a tutorial that may help with the underlying concepts.

We're here to help. Good luck.

[Haggrr]

For a rudimentary as my program is you were able to see through and set me on the correct course of action. For that I thank you. How do I mark this thread solved?

[June7]

Thread Tools dropdown above first post. Done.