All records from one side and if no records on other then put zero

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

Guest

Can someone help? Access 2000

I need all records from CarPart Table and only matching
records using CarPartID (Primary Key) from Parts, if there
are no records in Parts then I need zeros. At the
moment all I can get is matching records and therefore
not all the records from CarPart Table.

Thanks in advance
 
Try this:

SELECT CarPart.*, Nz(Parts.CarPartID, 0) AS PartID
FROM CarPart LEFT JOIN Parts
ON CarPart.CarPartID = Parts.CarPartID;
 
Izzy said:
Can someone help? Access 2000

I need all records from CarPart Table and only matching
records using CarPartID (Primary Key) from Parts, if there
are no records in Parts then I need zeros. At the
moment all I can get is matching records and therefore
not all the records from CarPart Table.
Click to expand...


Use an outer join.

SELECT CarParts.*, Parts.*
FROM CarParts LEFT JOIN Parts
ON CarParts.ID = Parts.CarPartID

You'll get Nulls instead of zeros, but you can work with
that.
 
Thanks for your help, but let me make it a bit more complicated.
I have 4 tables: CarSection - ID (Pkey, AutoNumber)
Description (Text)
CarPart - ID (Pkey, AutoNumber)
SectionID (Number)
Description (Text)
Parts - ID (Pkey, AutoNumber)
Price
ModelID (Number)
ColourID (Number)
BodyShapeID (Number)
VehicleDetailsID (Number)
etc
PartDetails - ID (Pkey, AutoNumber)
ModelID (Number)
CarPartID (Number)
LastPriced (Currency)
StockingLevel (Number)
PartsLocationID (Number)
ManufacturersPrice (Currency)
CostPrice (Currency)
NewLastPriced (Currency)

I need to be able to create a form/report for a specific model which shows
all records from CarPart table and matching records from PartDetails but
where there are no matching records to put zeros.

Thank you for your responses, it works fine for all records but when I want
to filter for a model it brings up only those matching records. Is there any
way I can filter the
PartDetails table for a specific model records but keep all CarPart records?

ie. so a report would look something like this -

Part No In
Stock Stocking Level

Body Panels

Body Shell 4 2 (Assuming 4 records in PartDetails
Front Bumper 1 0 for
this model)
Rear Bumper 0 2
N/S Wing 1 1
O/S Wing 0 1

Braking System

Brake Drum 2 4 (Assuming
2 records in PartDetails
for this model)
With thanks
 
Thanks for your help, but let me make it a bit more complicated.
I have 4 tables: CarSection - ID (Pkey, AutoNumber)
Description (Text)
CarPart - ID (Pkey, AutoNumber)
SectionID (Number)
Description (Text)
Parts - ID (Pkey, AutoNumber)
Price (Currency)
ModelID (Number)
ColourID (Number)
BodyShapeID (Number)
VehicleDetailsID (Number)
etc
PartDetails - ID (Pkey, AutoNumber)
ModelID (Number)
CarPartID (Number)
LastPriced (Currency)
StockingLevel (Number)
PartsLocationID (Number)
ManufacturersPrice (Currency)
CostPrice (Currency)
NewLastPriced (Currency)

I need to be able to create a form/report for a specific model which shows
all records from CarPart table and matching records from PartDetails but
where there are no matching records to put zeros.

Thank you for your responses, it works fine for all records but when I want
to filter for a model it brings up only those matching records. Is there any
way I can filter the
PartDetails table for a specific model records but keep all CarPart records?

ie. so a report would look something like this -

Part No In Stock Stocking Level

Body Panels

Body Shell 4 2 (Assuming 4 records in PartDetails for
this model)
Front Bumper 1 0
Rear Bumper 0 2
N/S Wing 1 1
O/S Wing 0 1

Braking System

Brake Drum 2 4 (Assuming 2 records in PartDetails for
this model)

With thanks
 
I apologize, I've been tied up all day on numerous work projects and did not
get a chance to post a reply. I will attempt to do that tomorrow morning (my
time).
 
I don't think I'm fully understanding what you want. The newsreader has
wrapped your lines, and it's a bit difficult to tell where one line ends and
another begins. Or where one table's field list ends and another table's
list begins.

Are you saying that the SQL expression I posted does not return a zero for
the calculated field's value if there is no matching record in Parts table?

Let me see if I'm reading correctly. You want to return records from a table
based on a model (is that CarParts table?), and also show matching records
from another table (is that PartDetails?), and if there is no matching
record in this second table, display a zero instead of a field from that
second table? What do you mean by "when I want to filter for a model it
brings up only those matching records"? By definition, when you filter a
query for a specific value in a field, the query will only select records
that contain that value in that field?

At the end of your question, it appears that you want to return all records
from a CarParts table (regardless of model), and return only matching
records from PartDetails where the record in PartDetails matches the desired
model information? Am I understanding this correctly? What would be the
purpose of all CarParts records if you're just interested in a single model?
Not trying to second-guess what you're doing, but rather to understand the
circumstances of what you want to do... helps to try to figure out the logic
of queries.

Also, can you post what you've tried to use as the SQL statement for the
query that isn't giving you the desired results?
 
Back
Top