Re: Please help, Report Prob

Hi there,
After trying out the report sometimes, i've successfully show the
delivery address and cust_code. Items are listed in the required area too,
however, the listed items are not listed accordingly to the delivery
address and cust_code. Kindly find below are my codes. FYI, i've created
a data grouping in the report and have the group expression of
invoice.inv_no = i_item.inv_no. Does it affect the items listed incorrectly?
Should i create the data grouping?

-----------------------------------------------------------
select sales.inst_name, ;
sales.inst_st1, ;
sales.inst_st2, ;
sales.inst_sub, ;
sales.inst_state, ;
sales.inst_pc, ;
sales.cust_code, ;
sales.inst_spec, ;
i_item.inv_no, ;
i_item.unit_meas, ;
i_item.item_desc, ;
i_item.qty, ;
invoice.con_no, ;
invoice.con_date ;
from i_item, sales, customer, invoice ;
where invoice.inv_no = i_item.inv_no ;
and sales.sales_no = invoice.sales_no ;
and sales.cust_code = invoice.cust_code ;
and customer.cust_code = invoice.cust_code ;
and customer.cust_code = sales.cust_code ;
and empty(invoice.con_no) ;
and customer.transport = "GMK" ;
order by sales.inst_name, ;
sales.inst_st1, ;
sales.inst_st2, ;
sales.inst_sub, ;
sales.inst_state, ;
sales.inst_pc, ;
sales.cust_code ;
group by sales.inst_st1, ;
sales.inst_st2, ;
sales.inst_sub, ;
sales.inst_state, ;
sales.inst_pc, ;
sales.inst_name, ;
sales.cust_code ;
into cursor curI_ITEM

if .not. used('company')
use company in 0
endif
select company

if .not. used('product')
use product in 0
endif
select product
set order to prod_code

if .not. used('s_item')
use s_item in 0
endif
select s_item
set relation to prod_code into product
set order to sales_no

if .not. used("i_item")
use i_item in 0
endif
select i_item
set order to inv_no

sele curI_item
set relation to inv_no into i_item
go top
report form testrepo2 preview
---------------------------------------------------------------------

As i mentioned before, i've to create the con_no and con_date once
the items are listed correctly accordingly to the cust_code and delivery
address, the con_no & con_date currently is empty in the table. I
would really appreciate if you can help me with this issue.

Awaiting for your reply.

Many thanks,
ShallowRacer

--
ShallowRacer


"swdev2" wrote:

1. I don't see any relation between the invoice table and the customer
table.
2. you don't care about the customer info ?
select * from i_item where inv_no in (select inv_no from invoice where
empty(con_no) == .t. and deliv_add1 in (select deliv_add1 from invoice) sort
by deliv_add1)
as a start ... then try isnull() as well.

3. then what about incrementing con_no for printing? ? i understand
consolidation, but suggest you assign the number PRIOR to printing it, using
what ever business rule you use for consolidation. Usually a consolidated
weigh bill has a unique number - its ok if you are using incremental #'s for
that, but i suggest 2 more tables - one for the consolidation #'s - 1:1 and
another for storing invoice to consolidating #'s (many to many, store keys
where cons_key can represent numerous invoice keys.) If you need further
help, contact me via email. [look for email addys in the sites below]

regards [Bill]

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

"ShallowRacer" <ShallowRacer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C3BA2835-0929-4F4F-879D-A1ABE17F147D@xxxxxxxxxxxxxxxx
Hi all,
Am currently doing a consignment note.
Use 4 major tables:
- customer(cust_code, transport)
- sales
- invoice(inv_no, deliv_add1, deliv_add2, deliv_sub, deliv_state,
deliv_pc,
con_no)
- i_item(inv_no, unit, item_desc, qty)

Aim is to grab the items which has empty con_no and are going to the same
deliv_add + display multiple results in single page and also incrementing
the
con_no in the new page header.

I've tried several methods like using SQL and create cursor/group_id and
also use the scan/scatter memvar technique. Still i've no clue how to do
that. Please help me with my prob.

Thanks v.much.

ShallowRacer



.

Relevant Pages

  • Re: Please help, Report Prob
    ... How can i separated different items according to delivery address into ... appropriate tables exactly the way you want to see it in your report. ... Customer talbe in your Select clause. ... what ever business rule you use for consolidation. ...
    (microsoft.public.fox.vfp.reports.printing)
  • Re: new record and form
    ... So i have 4 tab pages Registration, progress, delivery and baby, and after delivery care. ... I do believe however that it is important that your database models the entity type which is the relationship between a mother and her infants *in the context of the database*. ... As regards a report this is, as Phil has pointed out, more a question of creating a query which joins the relevant tables and returns rows for each shift. ...
    (microsoft.public.access.gettingstarted)
  • Re: Dealing with Duplicate Information
    ... it in summary format so I can retain it, add to it without duplicating, ... listed just one example from the report I'm working with. ... each seperate delivery number has ...
    (comp.databases.ms-access)
  • Re: Delivery reports... or NOT !!!!
    ... Just wondering if anyone else has this same problem that Delivery ... positive feedback that they got your e-mail. ... a delivery report can be used by spammers to validate their ... Acknowlegements to read receipt requests are handled by the recipient's ...
    (microsoft.public.outlook)
  • Re: Report Footer totals
    ... OrderID, CustomerCode, ShippedDate, DeliveryCharge etc) and tblOrderDetails ... Each line in the report is a summary of a sale (displayed in the OrderID ... My problem is displaying the total delivery charges for the report. ...
    (microsoft.public.access.modulesdaovba)