Needing Help with creating a query

[jbaccess]

Please see the attached document first.

I am trying to create a main query that I can then use to export all of this data into Pre-created Word documents.

I have tried several ways to create the query, changed relationships, etc., but I either get 1/2 of the data at a time (Top or bottom row information) or I get everything combined - but with several lines of repetition until I run out of inventory items.

I believe I need to be able to join the two together with a many-to-many relationship, but I'm not given (nor can I find) that option. I did try to create a Union.

SELECT Suppliers.[Accnt ID], Suppliers.Recycler, Suppliers.[Business Phone], Suppliers.[Fax Number], Suppliers.Address, Suppliers.Address2, Suppliers.City, Suppliers.[State/Province], Suppliers.[ZIP/Postal Code], Suppliers.[Country/Region], [Inventory Transactions Extended].[Transaction Item], [Inventory Transactions Extended].Quantity, [Inventory Transactions Extended].[Bid Control ID], [Inventory Transactions Extended].[Weight (lbs)], [Inventory Transactions Extended].[Pallet / Cage ID], [Inventory Transactions Extended].Bid, [Inventory Transactions Extended].[Shiper ID], [Inventory Transactions Extended].[Date Loaded], [Inventory Transactions Extended].[Date Shipped], [Inventory Transactions Extended].Recycler
FROM Suppliers INNER JOIN [Inventory Transactions Extended] ON Suppliers.ID = [Inventory Transactions Extended].[Transaction Type];

but as you'll see from the second image - it still is repeating the rows.


I am sure it's simple, what am I doing wrong.

Thanks in advance.

 

[plog]

Most likely you have a one to many relationship you don't think you have. My guess is its between the Suppliers and Suppliers Extended or Inventory Transactions Extended and Recyclers Query since they link via fields that are not primary keys.

You can see if this is true by running these queries:

SELECT ID, COUNT(ID) AS TotalRecords FROM [Suppliers Extended] GROUP BY ID ORDER BY COUNT(ID) DESC;

SELECT Recycler, COUNT(Recycler) AS TotalRecords FROM [Recyclers Query] GROUP BY Recycler ORDER BY COUNT(Recycler) DESC;

If anything other than '1' appears in the TotalRecords field that I am correct and you have multiple records in those data sources which are creating duplicates in the query you are trying to run.

 

[jbaccess]

Tried both ... didn't reveal anything.

I retried a union query pulling from just the tables after making sure that all the relationships were removed. I get the same results of having each item in the inventory repeated for each supplier/recycler.

Here's the query I ran.

SELECT [Inventory Transactions].[Transaction Item], [Inventory Transactions].[Pallet / Cage ID], [Inventory Transactions].Quantity, [Inventory Transactions].[Weight (lbs)], [Inventory Transactions].Bid, [Inventory Transactions].[Shiper ID], [Inventory Transactions].[Date Loaded], [Inventory Transactions].[Date Shipped], [Inventory Transactions].Recycler, Suppliers.Address
FROM [Inventory Transactions], Suppliers
GROUP BY [Inventory Transactions].[Transaction Item], [Inventory Transactions].[Pallet / Cage ID], [Inventory Transactions].Quantity, [Inventory Transactions].[Weight (lbs)], [Inventory Transactions].Bid, [Inventory Transactions].[Shiper ID], [Inventory Transactions].[Date Loaded], [Inventory Transactions].[Date Shipped], [Inventory Transactions].Recycler, Suppliers.Address;

 

[jbaccess]

You can see if this is true by running these queries:

SELECT ID, COUNT(ID) AS TotalRecords FROM [Suppliers Extended] GROUP BY ID ORDER BY COUNT(ID) DESC;

SELECT Recycler, COUNT(Recycler) AS TotalRecords FROM [Recyclers Query] GROUP BY Recycler ORDER BY COUNT(Recycler) DESC;

If anything other than '1' appears in the TotalRecords field that I am correct and you have multiple records in those data sources which are creating duplicates in the query you are trying to run.

ran these - results are '1''s in the first query, mostly '1's in the second as I do have some vendors multiple times because of multiple locations (maximum is 15)

 

[jbaccess]

I tried running through the wizard and got an error message that says that I cannot perform the query because the two tables are not related to each other (see attached). Yet I have a field "Recycler" in both tables? I'm really confused. Please advise. Thanks.

 

[plog]

Slow down, you are all over the place. So did you run my queries or not? Did either of them show results other than 1? If so, that's the reason for your duplicates--because its finding multiple values in one of the tables you are linking to.

 

[jbaccess]

SELECT ID, COUNT(ID) AS TotalRecords FROM [Suppliers Extended] GROUP BY ID ORDER BY COUNT(ID) DESC;

Resulted in only showing "1"s

SELECT Recycler, COUNT(Recycler) AS TotalRecords FROM [Recyclers Query] GROUP BY Recycler ORDER BY COUNT(Recycler) DESC;

Showed a 15, 3, 5, and the remaining were "1"s. I suspect this is due to having multiple vendor sites.

 

[plog]

There you go, that is the answer as to why you have multiple records in your query.

To eliminate them you must either eliminate the duplicate [Recycler] values, choose another field that is unique to link on or live with them.

 

[jbaccess]

Thanks for your help! I got it to finally work out with most of the relevant data showing. I have a new problem though now. Not all of the current records show when I run the query.

Here's my sql:

SELECT [Inventory Transactions].ID AS TransactionID, [Inventory Transactions].*, [Transaction Types].*, Inventory.*, [Inventory Transactions]![Weight (lbs)]-[Container Types.CTWeight] AS [Tare Weight], (Nz([Inventory Transactions].[Weight (lbs)],0)-Nz([Container Types].[CTWeight],0))*Nz([Inventory Transactions].[Bid],0) AS [Net Sale], IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]![Quantity],-([Inventory Transactions]![Quantity])) AS [Actual Quantity], Suppliers.*
FROM ([Transaction Types] INNER JOIN (Inventory INNER JOIN ([Container Types] INNER JOIN [Inventory Transactions] ON [Container Types].ID = [Inventory Transactions].[Container Weight]) ON Inventory.ID = [Inventory Transactions].[Transaction Item]) ON [Transaction Types].ID = [Inventory Transactions].[Transaction Type]) LEFT JOIN Suppliers ON [Inventory Transactions].Recycler = Suppliers.ID;

When I look at the [Inventory Transactions] table, the newest entries show up, but when I run the extended query to be used in a form - none of the newest entries are updating all the way through.

Thanks again.

 

[plog]

This too, has to do with the way you have linked your tables. If the data is in Inventory Transactions and the value you are linking to in another table doesn't exist, it will not show up in your query. For this you have two options:

1. Force it. Make all the joins from your Inventory Transactions LEFT INNER JOINS. That means right clicking the linking line between the tables and choosing the option in the dialog that pops up that says something like "show all records from Inventory Transactions and only those records from the other table where the joined fields are equal". This will force all the records in Inventory Transactions to show up, but will have null values for all the fields you are pulling from the second that don't have matching data.

2. Fix it. Find out which values are in Inventory Transactions that are not in your other tables and add them.