Hi All
I am a new user of Jet and I am trying to retrive some data from two tables. I have managed to link tables sucessfully and now just need to be able to retrive a comma list of the Product Codes from a child Table.
My data is structured like so
Header Table
OrderNo..DeliveryData…etc
123,01/02/2014,…
321,02/03/2014….
Child Table
OrderNo..ProductCode..Value
123,abc,20
123,def,30
123,ghj,50
321,xyz,20
321,vyh,30
I am trying to achive in excel
OrderNo,DaliveryDate,Products,OrderValue
123,01/02/2014,abc:def:ghj,100
321,02/03/2014,xyz:vyh, 50
So far, all I can achive is
OrderNo,DaliveryDate,Products,OrderValue
123,01/02/2014,abc,100
321,02/03/2014,xyz, 50
Can anyone point me in the right direction?
Date
Votes
1 comment
-
Jet Reports Historic Posts have you considered a grouping report? So that your report looks something like this:
OrderNo..DeliveryData…etc
123,01/02/2014,…
OrderNo..ProductCode..Value
123,abc,20
123,def,30
123,ghj,50
OrderNo..DeliveryData…etc
321,02/03/2014….
OrderNo..ProductCode..Value
321,xyz,20
321,vyh,30
this can be done by creating two different NL(ROWS commands. The first line would retrieve data from the header table, the second line would retrieve data from the child table (with the order number from the previous line brought down to use as a filter on the child table).
further detail on this can be found in the help files, search for "Grouping" report - or, if you need more help in the forum, please let us know.
The comma version you mentioned could likely be done with a JOIN command to bring in the product numbers - but you would have to first retrieve them in another section of your report as a list, then reference that list for the JOIN command.
Please sign in to leave a comment.