Displaying from different tables

[fareezf]

Hi,

I have a Access 2000 table named "Customer" with 7 fields. The first field is named "StyleCode". Also I have another table named "Style" with 2 fields namely "StyleCode" and "Style Description". I need to display the 7 fields of the Customer table in a Datagrid which is not a problem.

But in addition to that in that same Datagrid I need to display 'Style Description' from the other table if the 'StyleCode' from the Customer table matches the 'StyleCode' from the Style table. And that should be displayed next to the StyleCode field. How do I do that? Could someone please help me?

 

[Hypetia]

You need to generate this data using the following query.
___
[tt]
SELECT
Customer.StyleCode, Style.[Style Description],
Customer.Field2, Customer.Field3,
Customer.Field4, Customer.Field5,
Customer.Field6, Customer.Field7
FROM Customer
LEFT JOIN Style
ON Customer.StyleCode = Style.StyleCode;
___
[/tt]
You should substitute the correct field names for fields Field2 to Field7.

If you are using an ADO data control to connect to the database then you should specify the above query on the RecordSource property page of the ADO data control. (Command Text (SQL) text box)

 

[fareezf]

Hi Hypetia,

Thanks for your information. Tonight I'll give it a try and let you know the results.

 

[fareezf]

Hi,

It's me again. I typed what you provided me but it says "JOIN expression not supported". Please note I'm using VB 6, Access 2000 and in my form I'm using 'Adodc' control to which I am adding the above code in it's Recordsource.

Please help me.

 

[fareezf]

Hi Hypetia,

I fixed the JOIN problem by replacing the LEFT with INNER but please have a look at the below and see if you can help me.

I am using MsAccess 2000 and VB6 to display records from a table named 'CustomerOrders' in which there are 4 fields namely 'Style','Colour', 'OrderQty' and 'OrderSize'.

I'm using a Datagrid connected to a Adodc to display the records in the following manner.

STYLE COLOUR ORDERQTY ORDERSIZE
01 Blue 10 2
01 Blue 02 3
02 Blue 30 2
02 Green 40 4

Could somebody help me to do the following?

1. If the data is the same it should be left blank (e.g. instead of showing Blue 3 times once is enough followed by green).

2. How do I show a total field when the data for colour changes? (That is after the blue I need to show total = 42 and after green the total = 40 and so on..)

Any help will be much appreciated even if I have to use another method or control please let me know. I have spent more than two days in pulling my hair that I'm on the verge of looking like yul briner.

 

[Hypetia]

I doubt if you can prevent repeated appearences of the COLOUR field.
I think this cannot be done if your are using the Datagrid to display your record.

However, you can write your own code to display the data in a list view, for example, and fulfil your requirements there while writing your code.

Similarly, you cannot display the totals in the existing data grid. However, you can use the following query to generate a new recordset which will total the required field against the COLOUR field. See the query below.
[tt]
SELECT Colour, Sum(OrderQty) As SumOfQuantity FROM CustomerOrders GROUP BY Colour;[/tt]

It will render the data like this.
[tt]
Colour SumOfQuantity
Blue 42
Green 40
[/tt]
Once again, if you use a list view and write your own code to display the data, you will be able to merge this information into your original table and meet your customized requirements as well.