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

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
 
K

Ken Snell [MVP]

Try this:

SELECT CarPart.*, Nz(Parts.CarPartID, 0) AS PartID
FROM CarPart LEFT JOIN Parts
ON CarPart.CarPartID = Parts.CarPartID;
 
M

Marshall Barton

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.


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.
 
G

Guest

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
 
G

Guest

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
 
K

Ken Snell [MVP]

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).
 
K

Ken Snell [MVP]

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top