Access Report

[CatPlus]

Hello all,

Please help where do I start to write a report which will look in 2 tables, table 'Invoice_Header' (with columns 'invoice_number' and 'invoice_date') and table 'Invoice_Items' with columns 'invoice_number', 'Part_Number', 'Price' and 'Amount'. Invoice_Number being the linking column

Input of 'invoice_date_from' and 'invoice_date_to' and 'Part_Number' must bring all invoices within that date range with Part_Number specified. If date is left blank, then it must default 01/01/current year - current date

Thanks for your help!

Mickey Shekdar

 

[ZmrAbdulla]

Create a query using both the tables..
Use that query for the report..
to get the from/to date search for date criteria in these forums.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[Remou]

I think you will need:
A form showing the two dates and part number, with suitable default vales and checks;
A query based on Invoice_Header that uses the form for criteria;
A report based on the above query;
A query based on 'Invoice_Items' that gets part number from the form; and
A subreport based on the above query BUT ...

There may be a problem with producing invoices that do not show all the associated details, as far as I can remember invoices must be uniquely numbered and must not be changed.

 

[Remou]

(sorry ZmrAbdulla, forgot to refresh)

 

[CatPlus]

Hello Zameer Abdulla and Remou:

Thank you both for your replies... except I dont know where to begin. I have tried creating a form but when I begin, I am asked to choose a table from where the object's data comes from and I pick the table 'INVOICE_HEADER'. For columns (fields) I want on my form, I select 'INVOICE_DATE' since I need to specify the date range for my query. Now I have a form but can go from one record to another.

I am not sure how to create a form that will look like this:

Invoice date range:10/01/2005 to 10/31/2005 (October)
Part Number: 4100

When the query/report runs, it should look at all invoices dating in October, 2005 and get only those that included Part Number 4100 and give me the quantity and amout totals!

I'll appreciate if you will please advise a step-by-step process

Thanks indeed

Mickey

 

[ZmrAbdulla]

See Referential Integrity here
(

http://www.functionx.com/access/Lesson14.htm)

or an entire tutorial start from here..

http://www.functionx.com/access/index.htm

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.