Problem with my Query

[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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

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

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.

 

[Guest]

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.

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

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.

 

[Guest]

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

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.

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

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