Not Returning all rows

  • Thread starter Thread starter hockey6789player
  • Start date Start date
H

hockey6789player

I am trying to run a query that is basically a join of two tables.
Table 1:
Item Number
Item Description
Quantity

Table 2:
Item Number
BinLocation

Note: some items are not in table 2 (they have no location)

How do I get a append query to add to another table the Item Number,
Item Description, Quantity, and Bin Location? I can get it so I
returns all the items with a bin location, but I need to have the rows
returned that do not have locations, with a null value for their
location.

Is this possible?

Thanks,
Mark
 
Open the query in design view and double click on the line joining the two
tables between 'Item Number' fields.
Select the option to list all from Table1 and only those that match from
table2. Save. Run.
 
I am trying to run a query that is basically a join of two tables.
Table 1:
Item Number
Item Description
Quantity

Table 2:
Item Number
BinLocation

Note: some items are not in table 2 (they have no location)

How do I get a append query to add to another table the Item Number,
Item Description, Quantity, and Bin Location? I can get it so I
returns all the items with a bin location, but I need to have the rows
returned that do not have locations, with a null value for their
location.
Click to expand...


Change the join type to an outer join. Right click on the
line that joins the two tables and select the option that
includes all records from table 1 and any matching records
in table 2.
 
I have done this, although it still does not return all the rows.
Anything else I could be missing?

Thanks,
Mark
 
I have done this, although it still does not return all the rows.
Anything else I could be missing?

Thanks,
Mark
 
Sry bout the double post, it was supposed to be my SQL code, which is
as follows if that helps:
INSERT INTO 2ndBinLocations ( BinLocation, QtyAvailable )
SELECT DISTINCT ProductionEntry2ndLevel.*,
MB6_DataEntryDistributionDetai.BinLocation,
MB6_DataEntryDistributionDetai.Quantity
FROM ProductionEntry2ndLevel LEFT JOIN MB6_DataEntryDistributionDetai
ON ProductionEntry2ndLevel.ComponentItemCode =
MB6_DataEntryDistributionDetai.ItemNumber
WHERE (((MB6_DataEntryDistributionDetai.TransactionRefNumber)="P" &
[ProductionEntry2ndLevel]![ProductionEntryNumber]));

Thanks
 
Sry bout the double post, it was supposed to be my SQL code, which is
as follows if that helps:
INSERT INTO 2ndBinLocations ( BinLocation, QtyAvailable )
SELECT DISTINCT ProductionEntry2ndLevel.*,
MB6_DataEntryDistributionDetai.BinLocation,
MB6_DataEntryDistributionDetai.Quantity
FROM ProductionEntry2ndLevel LEFT JOIN MB6_DataEntryDistributionDetai
ON ProductionEntry2ndLevel.ComponentItemCode =
MB6_DataEntryDistributionDetai.ItemNumber
WHERE (((MB6_DataEntryDistributionDetai.TransactionRefNumber)="P" &
[ProductionEntry2ndLevel]![ProductionEntryNumber]));
Click to expand...


The are a different number of fields in the INSERT INTO
field list and in the SELECT field list. Don't use * to
select fields in the SELECT field list. List each field
that you need in the INSERT INTO field list.

You are using different table and field names than you did
in your original question so I am having trouble correlating
what you said earlier to what might be wrong now.

I suggest that you get the SELECT query working to your
satisfaction before making it an append query.
 
Back
Top