Need help on SHAPE command

[tttaff]

I'm trying to use the SHAPE command create a hierarchical recordset for a report in VB6. I'm using the Data Report Designer in VB and an Access database. This is a report of all client activity for a specified date range. I have a parent Client table with two child tables, FoodService and PaymentHistory.

Client table: ClientID, LastName, FirstName, Address, SocialSecurityNumber
----FoodService Table: FoodServiceID, ClientID, FoodServiceDate, FoodServiceType, NumberOfBags, Comments
....PaymentHistory table: PaymentID, ClientID, PaymentDate, PaymentType, AmountReceived, AmountRequested, Comments

Somehow, I want to combine the FoodService and PaymentHistory records for the specified date range into one child record under the parent Client record that would look something like this:

Parent Client: ClientID, LastName, FirstName, Address, SocialSecurityNumber
------ Child: ServiceDate, ServiceType ("Food" or "Payment"), ServiceDescription, Amount1 (number of bags), Amount2 (amount requested), Amount3 (amount received),ServiceComments

Since the two child tables are a little different from each other, I don't know how to combine them into one record that I can then use to create one line item for the report.

Is there a way to do this with the SHAPE command?

Tim

[dglienna]

Here's a SHAPE example. Not sure what you mean, but it should help.

[tttaff]

Thanks, but not exactly what I'm looking for. I'm familiar with using the SHAPE command and use it in some other places, but I'm a bit confused as to how or even if I can use it in the specific way I need here.

Tim

[hensa22]

I suggest you create a view on your 2 child tables

create or replace view myview as 
select  FoodServiceID, ClientID, FoodServiceDate, FoodServiceType, NumberOfBags, Comments from FoodService 
UNION
select PaymentID, ClientID, PaymentDate, PaymentType, AmountReceived, AmountRequested, Comments from PaymentHistory

take care of data type in UNION statement, each number of column has to have same data type.
sample

FoodServiceID and PaymentID,FoodServiceDate and PaymentDate and so on, they must have same data type

now, as you have your view named myview , then it uses with your table Cliente and link on relation 'ClientID'.

[tttaff]

<take care of data type in UNION statement, each number of column has to have same data type.>

Yes, I thought of that but my problem is the two tables do not have the same number of fields. FoodService has NumberOfBags (which is integer), and Payments has AmountReceived and AmountRequested (which are both currency), so a UNION won't work.

Tim

[dglienna]

Sounds like you have a DB design problem. You should normalize your tables. I hope you don't have a field for each BAG in the table.

[hensa22]

Yes, I thought of that but my problem is the two tables do not have the same number of fields. FoodService has NumberOfBags (which is integer), and Payments has AmountReceived and AmountRequested (which are both currency), so a UNION won't work.
Tim

firstly in your problem data type you could use CAST.

select fields1::datatype1 table1
  UNION
select field1::datatype1 table2

and about number columns you could :

select fiald1,fields2::datatype1 table1
  UNION
select field1,null::datatype1 table2

then there no problem.

but
I hope that you look at the post David wrote, too

[tttaff]

Sounds like you have a DB design problem. You should normalize your tables. I hope you don't have a field for each BAG in the table.

No, there is only one field that stores the number of bags of groceries a client receives for any one date.

This database is for a community outreach center that provides both food and monetary assistance to needy people. The idea is that a client can come in and receive one or more bags of groceries, or receive help in paying their monthly bills, and we want to track those instances. We want to track each time they come in and receive groceries and how many bags they received. Same with payment assistance.

I have two tables each linked to the master Client table, FoodService and Payments. They are set up as follows:

FoodService table:
...FoodServiceID
...ClientID
...FoodServiceDate
...FoodServiceTypeID
...NumberOfBags
...Comments

Payments:
...PaymentID
...ClientID
...PaymentDate
...PaymentsTypeID
...AmountPaid
...AmountRequested
...Comments

If I were to "normalize" my tables, how else would I do it?

[jp140768]

Using Union, it shouldn't matter that there are a different number of fields in each table, unless you are doing Select *, provided you select the same number of fields in each table, and the corresponding fields must be of a compatible type.

If the fields are different, then as suggested below you can use something like CAST to convert them, if you have more fields in one than the other, you could always select dummy fields eg Select 'A' as dummy1 to bring up the number.

HTH