Solved Query-Graph (1 Viewer)

[Alejo]

Hi AWF Team,

I would like to ask your help on my database.
Below is combine screenshot of table, query and chart (chart is linked to query).
On the query, I filtered the "Customer" for CustA, CustB, CustC by Group (refer to the attached DB), however no value was generated due to other condition of my query.

No value is generated which is still fine but I would like to have at least the items CustA, Cust,B and CustB to appear in Query under "Customer" like:
Customer
CustA
CustB
CustC

Then I will link the graph to the query to have the below display in the graph.
Please help me for this query and you can directly put the solution to the attached DB.

 

[ebs17]

If you filter, you lower the result to be displayed.

WHERE
   Table1.D_Date > #9/1/2022#
            AND
   Table1.D_Date < #9/1/2023#

This filter alone can never be satisfied by a value. Do you know why?
So be less aggressive when filtering.

 

[Alejo]

If you filter, you lower the result to be displayed.

WHERE
   Table1.D_Date > #9/1/2022#
            AND
   Table1.D_Date < #9/1/2023#

This filter alone can never be satisfied by a value. Do you know why?
So be less aggressive when filtering.

Hi Ebs, thanks for the response.

I agree with you but it is expected that due to that filtering the values cannot be generated.
It's just to show that in that particular dates and with the given condition, there were no transaction with customers.

For cases like that, I just need to display the CustA, CustB and CustC in the at the bottom of graph (and thru query). Empty graph is not acceptable in my presentation.

 

[Alejo]

This function is possible in excel chart but I dont know how to do it in access.
I'm using 2010 access version fyi.

 

[ebs17]

Oh, that's what you mean.
You should do some data modeling first. You need a customer table and a product table as well as a link table for the assignment of customer to product (via their ID's).
In the query you link via OUTER JOIN:

...
FROM tblCustomers LEFT JOIN tblCustomersProducts ON ...

 

[June7]

Query for the graph has to link Customers table with the filtered query, like:

SELECT Customers.CustName, qryAvgCustProdA.AVG_Cust
FROM Customers LEFT JOIN qryAvgCustProdA ON Customers.CustID = qryAvgCustProdA.Customer;

 

[Alejo]

Guys,
Sorry but I cant translate the solutions to my DB. Im not expert in the access field.

Would it be OK if you can apply the solution direct to the DB so.I can follow.

 

[June7]

1. Build a table Customers with fields CustID (autonumber) and CustName (text), add records
2. Remove data from Customer field in Table1, change it to number type
3. Populate Customer field with CustID from Customers table
4. Build query shown in post 6
5. Use that query as data source for chart

 

[arnelgp]

see if this is what you need.

 

[Alejo]

see if this is what you need.

Hi Arnelgp, Jun7

Yes this is the expected out I need. I can use it if the DB has just started.

But our database is populated already and continously adding more information. It's not advisable anymore to add or change as per your suggestion or else it will require major changes.

Maybe the only thing I can now is that... would it be possible to do the required output using any codes without adding anymore tables?

 

[June7]

You need a dataset of all customers. If you don't want to create a table, you can pull customers from the existing data.

SELECT DISTINCT Customer FROM Table1;

Name the query Customers, save it, and use it like table. Or build a nested query:

SELECT Customers.Customer, qryAvgCustProdA.AVG_Cust
FROM (SELECT DISTINCT Customer FROM Table1) AS Customers
LEFT JOIN qryAvgCustProdA ON Customers.Customer = qryAvgCustProdA.Customer;

 

[arnelgp]

i remove the tables and just leave the queries.
can you make the queries?

 

[Alejo]

i remove the tables and just leave the queries.
can you make the queries?

Thanks Arnelgp for always providing the solution into the DB file, il check and confirm..


Thanks June7 for your suggestion

 

[Alejo]

Thanks Arnelgp for always providing the solution into the DB file, il check and confirm..


Thanks June7 for your suggestion

HI Arnelgp,
Successfully applied the solution to my DB and works fine.

Just want to know:
1. why you put "D" as criteria in the query?
2.Instead of "0" value, would it be possible to be "NA"?

This is already solved. Il do that after your response.

 

[arnelgp]

1. if "D" is not the criteria, you put the correct one.
2. it's numeric so, i use 0.

 

[Alejo]

1. if "D" is not the criteria, you put the correct one.
2. it's numeric so, i use 0.

Can I change it to display NA? How

 

[arnelgp]

in the graph, no. the result of the graph is Numeric.
that is 0 or any number.
if you want, you can have a Label control that will show "N/A" when
the result is 0.
you may even "hide" the chart and just show the Label control.

 

[Alejo]

Solved, we explained