Returning multiply entries on Query

[Nancy]

I have a table, a query of the table, a main purchase order form and subform
with a record source of the query and a report utilizing another query as the
record source.

Table: Purchase Order Table
Purchase Order Detail Table

Query: Orders Query
Order Details Query
Order Sumary; joins the two queries together

Form: Main Purchase Order Form
Order Details Subform

Report: Purchase Order Report
Orders Summary with a record source of "Orders Summary Query"

When I enter info into the purchase order, everything works fine. I click
my purchase order report and it looks fine. I check orders table and the
order details table and everything looks fine. When I check my Orders
Summary Query, there are certain Order ID #'s that are repeated multiple
times. When I check my Orders Summary Report they are also listed multiply
times. What is going on? I tried deleting that particular purchase order
number information and then reentering and the same thing happened. I
entered about 15 different purchase orders some had single orders and some
had multiple orders. But when the orders summary report prints I get the
multiple entries and not on each and ever entry on about three. When the
entries are printed mulitpe times in the report then the totals are
calculated based off the incorrect information. Any ideas?

Thanks

 

[NetworkTrade]

well queries are never wrong; they may not be what you expect or understand,
but they aren't wrong....

can't say why there are duplicate values off hand. Consider in the query
design where you have multiple tables/queries that you can modify the
relationship (the line between the tables) and change that to All records of
one table and just matching from the other... will change that line to an
arrow pointing one way or the other.

also in the properties of the query design you can select 'Unique Values' to
make it a distinct query

 

[John W. Vinson]

When I enter info into the purchase order, everything works fine. I click
my purchase order report and it looks fine. I check orders table and the
order details table and everything looks fine. When I check my Orders
Summary Query, there are certain Order ID #'s that are repeated multiple
times.

If you join a "one" side table to a "many" side table in a query, you will
indeed get each "one" side record repeated for each "many" side record. That's
precisely how queries are designed and intended to work.

It may be that you need to either change the query, or change the design of
your report; you can use the Sorting and Grouping feature to group by the
Order ID, and put order specific information in the group header and/or
footer, and order detail information in the Detail section of the report.

 

[Nancy]

Here is what I have now. I do not totally understand this relationship
stuff. I keep reading and reading so at least I am learning. This is what I
have under the Relationships tab:

Order Table: Order ID-(Primary Key)
Vendor ID, EmployeeID, OrderDate, PurchaseOrderNumber, etc

Order Detail Table: OrderDetailID-(Primary Key)
OrderID, PurchaseOrderNumber, Nomenclature, etc.

I have a #1 in the OrderTable: Order ID and a Many on the OrderDetailsTable:
OrderID
One-To-Many relationship.
Join type is #1†Only include rows where the joined fields from both tables
are equal.
Enforced Referential Integrity is marked.

On the Order Summary Query in design view I have the following:

Order Details Totals: OrderID--- Linked to: Orders: OrderID (as the
primary key)

Order: OrderID(Primary Key)-----Linked to OrdersDetailsExtended----OrderID
Both are: Only include rows where the joined fields from both tables are
equal.

Any ideas on best way to approach the change. I am going to go back and
look at the report about maybe doing the grouping changes. Never done that
before either. I took this Report from a template and have just changed the
names to meet my requirements. Maybe I missed one of the changes that I
should have made.

I deeply appreciate all the help I have received on this wonderful site.
You all are the best at teaching. I check the website daily and read a lot
about all the different subjects and I am learning something new each day.
What a great site.

Thanks

 

[John W. Vinson]

Here is what I have now. I do not totally understand this relationship
stuff. I keep reading and reading so at least I am learning. This is what I
have under the Relationships tab:

Order Table: Order ID-(Primary Key)
Vendor ID, EmployeeID, OrderDate, PurchaseOrderNumber, etc

Order Detail Table: OrderDetailID-(Primary Key)
OrderID, PurchaseOrderNumber, Nomenclature, etc.

I have a #1 in the OrderTable: Order ID and a Many on the OrderDetailsTable:
OrderID
One-To-Many relationship.
Join type is #1” Only include rows where the joined fields from both tables
are equal.
Enforced Referential Integrity is marked.

On the Order Summary Query in design view I have the following:

Order Details Totals: OrderID--- Linked to: Orders: OrderID (as the
primary key)

Order: OrderID(Primary Key)-----Linked to OrdersDetailsExtended----OrderID
Both are: Only include rows where the joined fields from both tables are
equal.

The best way to communicate a Query on the newsgroups is to open the query in
SQL view and copy and paste the SQL code to a message. It may look cryptic at
this point, but lots of us can read SQL text as easily as we read the
newspaper; and the SQL is the *real* query.

Any ideas on best way to approach the change. I am going to go back and
look at the report about maybe doing the grouping changes. Never done that
before either. I took this Report from a template and have just changed the
names to meet my requirements. Maybe I missed one of the changes that I
should have made.

Well... since I don't know what you're doing, nor more than a vague idea what
you're trying to do, it's hard to advise, other than to say yes, the query is
working as it should (returning the Orders fields in conjunction with each
record in Order Details Totals), and that one possible solution is to change
the query to give what you want; another is to change the report to display
details data in the detail section, and orders data in the Orders header.
Perhaps you could post the SQL view of the query, a short sample of the
results of the query, and indicate what you would like to see.

I deeply appreciate all the help I have received on this wonderful site.
You all are the best at teaching. I check the website daily and read a lot
about all the different subjects and I am learning something new each day.
What a great site.

Thanks! It's not a "site" though, it's a NNTP newsgroup; there are various
websites (including Microsoft's) which provide an interface to it, but it's
actually not a website itself.

 

[Nancy]

Here is the SQL for the Order Summary Query. I would like the Order Summary
Report to give me an order summary of purchases made from various vendors
over a specific period of time. I can filter it later. As best I can tell
from my limited knowledge with Access (I am using 2007), I believe my problem
lies in this query. I have entered a total of 16 purchase orders to test
this database. Some of the purchase orders have dollar amounts entered into
the Purchase order form and some do not. I checked each purchase order form
to make sure all of the entries were correct. I then went to each table and
query to check to make sure the entries were correct. Everything seems to
look fine until I come to the Order Summary Query. Of the sixteen entries,
fourteen looked fine and two returned double entries. I.e.

I entered:
Purchase order # 7488-4380; OrderID 9; Boot, PN XYZ-123
# 7488-4380; OrderID 9; Boot, PN 123-XYZ

What I get is: # 7488-4380;OrderID 9; Boot, PN XYZ-123
#7488-4380; OrderID 9; Boot, PN 123-XYZ
# 7488-4380; OrderID 9; Boot, PN XYZ-123
#7488-4380; OrderID 9; Boot, PN 123-XYZ

Case #2 I entered:

#7482-4380; OrderID 3; Drill Bit, PN 15-DM10
#7482-4380; OrderID 3; Drill Bit; PN 15-DM20
#7482-4380; OrderID 3; Drill Bit; PN 15-DM30
#7482-4380; OrderID 3; Drill Bit; PN 15-DM40

What I get is: #7482-4380; OrderID 3; Drill Bit, PN 15-DM10
#7482-4380; OrderID 3; Drill Bit; PN 15-DM20
#7482-4380; OrderID 3; Drill Bit; PN 15-DM30
#7482-4380; OrderID 3; Drill Bit; PN 15-DM40
#7482-4380; OrderID 3; Drill Bit, PN 15-DM10
#7482-4380; OrderID 3; Drill Bit; PN 15-DM20
#7482-4380; OrderID 3; Drill Bit; PN 15-DM30
#7482-4380; OrderID 3; Drill Bit; PN 15-DM40

Again, the double entries only show up in the Orders Summary Query and
Report. They do not show as double entries in any underlying tables or
queries.

SELECT Orders.OrderID, [Order Details Extended].[Line Item Total],
Nz([SumOfLine Item Total])+Nz([Taxes]) AS [Order Totals], Orders.VendorID,
Orders.EmployeeID, Orders.OrderDate, Orders.PurchaseOrderNumber,
Orders.ShipDate, Orders.ShippingMethodID, Orders.Comment, [Order Details
Totals].[Tail #], [Order Details Extended].Nomenclature, [Order Details
Extended].[Part Number], Orders.[Core Charge], Orders.[Core Due], [Order
Details Extended].Condition
FROM ([Order Details Totals] INNER JOIN Orders ON [Order Details
Totals].OrderID = Orders.OrderID) INNER JOIN [Order Details Extended] ON
Orders.OrderID = [Order Details Extended].OrderID;

Thank you for your help.

 

[John W. Vinson]

I entered:
Purchase order # 7488-4380; OrderID 9; Boot, PN XYZ-123
# 7488-4380; OrderID 9; Boot, PN 123-XYZ

What I get is: # 7488-4380;OrderID 9; Boot, PN XYZ-123
#7488-4380; OrderID 9; Boot, PN 123-XYZ
# 7488-4380; OrderID 9; Boot, PN XYZ-123
#7488-4380; OrderID 9; Boot, PN 123-XYZ

That is precisely what I would expect you to get, and is precisely what your
query is asking for.

SELECT Orders.OrderID, [Order Details Extended].[Line Item Total],
Nz([SumOfLine Item Total])+Nz([Taxes]) AS [Order Totals], Orders.VendorID,
Orders.EmployeeID, Orders.OrderDate, Orders.PurchaseOrderNumber,
Orders.ShipDate, Orders.ShippingMethodID, Orders.Comment, [Order Details
Totals].[Tail #], [Order Details Extended].Nomenclature, [Order Details
Extended].[Part Number], Orders.[Core Charge], Orders.[Core Due], [Order
Details Extended].Condition
FROM ([Order Details Totals] INNER JOIN Orders ON [Order Details
Totals].OrderID = Orders.OrderID) INNER JOIN [Order Details Extended] ON
Orders.OrderID = [Order Details Extended].OrderID;

This is joining each record in Order Details Total, to each record in Orders,
and then to each record in Order Details Extended. If there are two records in
Order Details Extended for a particular OrderID then yes, you will see the
order twice, once with each value.

I guess I don't understand what you expect. What are the primary keys of the
three table? If you're not pulling any fields from [Order Details Totals] do
you need to include it in the query at all?

 

[Nancy]

Thank you John for your response. I went back and read all your responses,
then I went and pulled my head out of my dufflebag and looked at it again.
Not excatly sure if I totally understand what I did, but it works now. I
redesigned the query and the report. Everything seems to be working
correctly now. I went back and double/triple checked and everything seems to
be working as I want it too.

Thank you again for your help. Sorry if I was not explaining it clearly.

I entered:
Purchase order # 7488-4380; OrderID 9; Boot, PN XYZ-123
# 7488-4380; OrderID 9; Boot, PN 123-XYZ

What I get is: # 7488-4380;OrderID 9; Boot, PN XYZ-123
#7488-4380; OrderID 9; Boot, PN 123-XYZ
# 7488-4380; OrderID 9; Boot, PN XYZ-123
#7488-4380; OrderID 9; Boot, PN 123-XYZ

That is precisely what I would expect you to get, and is precisely what your
query is asking for.

SELECT Orders.OrderID, [Order Details Extended].[Line Item Total],
Nz([SumOfLine Item Total])+Nz([Taxes]) AS [Order Totals], Orders.VendorID,
Orders.EmployeeID, Orders.OrderDate, Orders.PurchaseOrderNumber,
Orders.ShipDate, Orders.ShippingMethodID, Orders.Comment, [Order Details
Totals].[Tail #], [Order Details Extended].Nomenclature, [Order Details
Extended].[Part Number], Orders.[Core Charge], Orders.[Core Due], [Order
Details Extended].Condition
FROM ([Order Details Totals] INNER JOIN Orders ON [Order Details
Totals].OrderID = Orders.OrderID) INNER JOIN [Order Details Extended] ON
Orders.OrderID = [Order Details Extended].OrderID;

This is joining each record in Order Details Total, to each record in Orders,
and then to each record in Order Details Extended. If there are two records in
Order Details Extended for a particular OrderID then yes, you will see the
order twice, once with each value.

I guess I don't understand what you expect. What are the primary keys of the
three table? If you're not pulling any fields from [Order Details Totals] do
you need to include it in the query at all?