Problem with my Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that tracks all credits issued by our department. My
supervisor's report needs to separate data by orders and renewals (which I
record in a field called function).

I created a simple query that limits the function by either "orders" or
"renewals". When running this query, everything runs as it should.

Unfortunately, with the more complicated queries (that manipulate more
complex data for reporting), the functions get mixed up.

For some reports the effects are only a few records and for others, many
records are affected. I originally was limiting the function with a
user-entered parameter. However, even when I specify one or the other on the
query, things get jumbled.

Has anyone seen or heard of something like this? Any advice would be
qreatly appreciated.

Thank you,
Jim
 
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.
 
Hi Jerry,

Thanks for responding. I should have thought to send the SQL with my
original post. The first SQL is for the basic query that works:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function
FROM tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor
WHERE (((tblRequestor.Function)=[Enter function]));

This next SQL is for the query with problems:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest, tblTransactionRequest.ForcedCreditRequest
FROM (tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) INNER JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblRequestor.RequestorName, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=550) AND
((tblRequestor.Function)=[Enter Funtion]) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report])) OR (((tblTransactionRequest.Division)=571))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

I hope it is something simple that was overlooked. Please advise.

Sincerely,
Jim
 
Hi Again,

A friend suggested that the join types on my queries needed changing. I
tried this with no success. But just in case he was on the right track, I
thought I would re-paste my SQL.

Again, this is the SQL for the simple (working) query:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function
FROM tblRequestor RIGHT JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor
WHERE (((tblRequestor.Function)=[Enter function]));

And this is the SQL for the more complicated (not working) query:

SELECT tblTransactionRequest.TransactionID, tblRequestor.RequestorName,
tblRequestor.Function, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest, tblTransactionRequest.ForcedCreditRequest
FROM (tblRequestor RIGHT JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) RIGHT JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID, tblRequestor.RequestorName,
tblRequestor.Function, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
HAVING (((tblRequestor.Function)=[Enter Funtion]) AND
((tblTransactionRequest.Division)=550) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report])) OR (((tblTransactionRequest.Division)=571))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product,
tblTransactionRequest.ReasonCode;

If anyone can offer some resolution on this, it would be greatly appreciated.

Sincerely,
Jim

Jim Johnson said:
Hi Jerry,

Thanks for responding. I should have thought to send the SQL with my
original post. The first SQL is for the basic query that works:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function
FROM tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor
WHERE (((tblRequestor.Function)=[Enter function]));

This next SQL is for the query with problems:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest, tblTransactionRequest.ForcedCreditRequest
FROM (tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) INNER JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblRequestor.RequestorName, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=550) AND
((tblRequestor.Function)=[Enter Funtion]) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report])) OR (((tblTransactionRequest.Division)=571))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

I hope it is something simple that was overlooked. Please advise.

Sincerely,
Jim

Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.
Click to expand...
Click to expand...
 
I noticed a few things:

1. tblTransactionRequest.RequestDate is part of the Group By but not in the
Select portion. That could cause records to show up that look like duplicates
otherwise. BTW if you wanted the RequestDate to be returned, I could show you
how to simplify the query a lot.

2. Speaking of RequestDate, is it a text or Date/Time field? If a text
field, it could cause errors. Also while in the design view of the query, you
should go up to Query, Parameters and define all three parameters

3. OR (((tblTransactionRequest.Division) = 571)) If I counted the number
of () correctly, this OR clause will cause any record with 571 to show up.
Anything but 571 will have to match the rest of the Where clause. Is that
what you want?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jim Johnson said:
Hi Jerry,

Thanks for responding. I should have thought to send the SQL with my
original post. The first SQL is for the basic query that works:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function
FROM tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor
WHERE (((tblRequestor.Function)=[Enter function]));

This next SQL is for the query with problems:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest, tblTransactionRequest.ForcedCreditRequest
FROM (tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) INNER JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblRequestor.RequestorName, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=550) AND
((tblRequestor.Function)=[Enter Funtion]) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report])) OR (((tblTransactionRequest.Division)=571))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

I hope it is something simple that was overlooked. Please advise.

Sincerely,
Jim

Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.
Click to expand...
Click to expand...
 
Hi Jerry,

Thank you so much for your help! My main problem was that I had that
separate criteria for the division. Once I changed the criteria to "550 Or
571" it worked like a charm.

The RequestDate field is not text but a date (mm/dd/yyyy). I only use the
date to narrow the data but did not include it in the report, as it seemed to
work fine without it. I am not sure what you meant by "define all three
parameters".

I am a novice developer and really want to learn more about Access. So, any
wisdom you can impart on me would be very helpful. With each problem that I
receive help from the discussion board, I learn something new.

Sincerely,
Jim

Jerry Whittle said:
I noticed a few things:

1. tblTransactionRequest.RequestDate is part of the Group By but not in the
Select portion. That could cause records to show up that look like duplicates
otherwise. BTW if you wanted the RequestDate to be returned, I could show you
how to simplify the query a lot.

2. Speaking of RequestDate, is it a text or Date/Time field? If a text
field, it could cause errors. Also while in the design view of the query, you
should go up to Query, Parameters and define all three parameters

3. OR (((tblTransactionRequest.Division) = 571)) If I counted the number
of () correctly, this OR clause will cause any record with 571 to show up.
Anything but 571 will have to match the rest of the Where clause. Is that
what you want?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jim Johnson said:
Hi Jerry,

Thanks for responding. I should have thought to send the SQL with my
original post. The first SQL is for the basic query that works:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function
FROM tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor
WHERE (((tblRequestor.Function)=[Enter function]));

This next SQL is for the query with problems:

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblRequestor.RequestorName,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest, tblTransactionRequest.ForcedCreditRequest
FROM (tblRequestor INNER JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) INNER JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblRequestor.RequestorName, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=550) AND
((tblRequestor.Function)=[Enter Funtion]) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report])) OR (((tblTransactionRequest.Division)=571))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

I hope it is something simple that was overlooked. Please advise.

Sincerely,
Jim

Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a database that tracks all credits issued by our department. My
supervisor's report needs to separate data by orders and renewals (which I
record in a field called function).

I created a simple query that limits the function by either "orders" or
"renewals". When running this query, everything runs as it should.

Unfortunately, with the more complicated queries (that manipulate more
complex data for reporting), the functions get mixed up.

For some reports the effects are only a few records and for others, many
records are affected. I originally was limiting the function with a
user-entered parameter. However, even when I specify one or the other on the
query, things get jumbled.

Has anyone seen or heard of something like this? Any advice would be
qreatly appreciated.

Thank you,
Jim
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I ignore duplicates? 2
Use In() Expression as Query criteria with guts from Function 1
Multiple Records being returned in Query 2
Multiple supervisors over a date range in query 1
Query Parameter Function 2
No results when adding 2nd table to query 1
Access MS Access DCount function problem 0
Left join in query 12

Back
Top