[smaaq]

Dear Sir,
I have am facing problem in access programme development if you can do so please solve it at the earliest.
I have to created 5 tables for the determination of sales individually and then merged all the tables to each other to find out the actual transaction in the whole day.
Fields of Table-1 Name (Expenses )
Field Name Field Type
Serial No. Auto Number
Date Date
Particulars Text or Lookup
Expenses Amount Numeric
Fields of Table-2 Name (Opening Balance)
Field Name Field Type
Date Date
Name Text or Look Up
Account No Number
Opening Balance Number
Fields of Table-3 Name (Consumers)
Field Name Field Type
Consumer ID Auto Number
Name Text
Account No Number
Fields of Table-4 Name JOURNAL
Field Name Field Type
Serial No. Auto Number
Date Date
Account No Number
Name of Consumer Text Or Look Up
Opening Balance Number
Particulars-1 Description Text or Look Up
Particulars-2 Sale of Oil Text Or LookUp
Quantity Number
Rate Number
Bill Amount (Debit Amount) Number
Credit Amount (if Credit Sale) Number
Closing Balance Number IN Query the Field of Closing Balance : [Opening Balance] [Bill Amount]-[Credit Balance]
Cash Transfer Number

Fields of Table-5 Name (Company Registery)
Field Name Field Type
Serial No. Auto Number
Date Date
Bank Reference No Number
Book No Number
Amount Transfer To Company Number
Invoice Amount Number
Balance Number
Afater All I have to make a query of all 5 tables. I have to merge all 5 tables and in the field of closing balance is calculated like above [Opening Balance] [Bill Amount]-[Credit Balance] then list all records retrieve from all tables but it should shown the difference in balance of each consumer.
After That, I have to make a Daily Report and in the report I have to display
Total Bill Amount of the Day
Total Credit Amount of the Day
Total Expenses of the Day.
Please do with yourself then send me at my email id. And guide me how can I do this.
{E-mail removed}

[ScottGem]

If you have a query that displays the information you want, then use the Report wizard to generate a report. After the Report wizard generates the report you can customize it as needed. When using the wizard you want to setup a grouping by company so you can display all the daily transactions then a summary by company.

I do have some suggestions on the structure of your database

(Expenses )
Field Name Field Type
ExpensesID (PK Auto Number - instead of Serial No. )
ExpenseDate (Date-Date is a reserved word and shouldn't be used for object names)
Particulars (Text-This sounds like it should be pulling from a lookup table, but don't use a lookup field)
ExpenseAmount (Currency - you should not use spaces in object names)

(Consumers)
ConsumerID (PK Auto Number)
ConsumerName (Text- If this is a person's name, it should be borken out into its components, first name, surname etc. Also Name is also a reserved word)
AccountNo (Text-Number data types should only be used for numbers used in calculations)

(JOURNAL)
JournalID (PK Auto Number)
JournalDate (Date)
AccountNo (Text)
ConsumerID (Foreing Key Long Integer)
Description (Text)
Particulars-2 (Text)
Quantity (Number)
Rate (Number)
BillAmount (Currency)
CreditAmount (Currency)
CashTransfer (Currency)

(Company Registery)
RegistryID (PK Auto Number )
RegistryDate (Date)
BankReference (Text)
BookNo (Text)
TransferAmount (currency)
InvoiceAmount (Currency)


Balance should be calculated, not stored. I'm not even sure what that last table is about.