Need help on SHAPE command
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
[1349 byte] By [
tttaff] at [2007-11-20 8:46:23]
# 1 Re: Need help on SHAPE command
Here's a SHAPE example. Not sure what you mean, but it should help.
# 2 Re: Need help on SHAPE command
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
tttaff at 2007-11-9 13:46:22 >
# 3 Re: Need help on SHAPE command
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'.
# 4 Re: Need help on SHAPE command
<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
tttaff at 2007-11-9 13:48:17 >
# 5 Re: Need help on SHAPE command
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.
# 6 Re: Need help on SHAPE command
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
# 7 Re: Need help on SHAPE command
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?
tttaff at 2007-11-9 13:51:20 >
# 8 Re: Need help on SHAPE command
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
