Ms Access 2003 Table and Form Query

Hi,


I have only 2 table (1- Client contact & 2- billing details)
I am designing a database for billing in which i need to have the below fields

SrNo Description of goods Qty Rate PerPcs


I wan the above fields for each and every invoice to be repeated but Ms Access has limitation of 32 indexes to be repeated.


What i want to display in the form is as below

SrNo Description of goods Qty Rate PerPcs
1 ABCD 15 5 pcs
2 XYZ 15 20 pcs

This is only for one invoice and 2 items. But sometimes i have 15 items and in which i can't repeat the same Serial no, description, qty, rate and pcs.

re: Ms Access 2003 Table and Form Query

Welcome to Bytes!

I'm confused. What exactly do you want help with?

re: Ms Access 2003 Table and Form Query

I have 2 forms and 2 tables as below
1st Client Contact
2nd Bill Details


Client Contact contains the below fields
Client Id, Company Name, Address, Ph, Mob, Email, Website


Bill Details Contains below fields
Invoice Id, Invoice Date, Client Id, Company Name, Address, Sr.NO., Description of goods, qty and rate.


I want to first link the Bill details - Client Id with Client Contact-Client Id so that it displays me the list of id's that are in CLient Contac.

Then once i click on the a specific id it should automatically display the Company Name and Address related to that particular id.

Please let me know if you need more information

re: Ms Access 2003 Table and Form Query

This sounds like you first need a query that links the two tables together. This would include all the details you require to be displayed. Have you got this part done already?

When you have that you can think about how to show the Name & Address info. I'm not sure this requires anything special generally. You can probably fit it on the main part of the form for all records if you wanted to.

Anyway, let me know where you're up to and I can look at providing the specific help that's required.

re: Ms Access 2003 Table and Form Query

I haven't created query yet.

So i should first create the query and then what should i do to get the field automatically populated in the form. I guess the form that i will create will be from Query and not table.

I have already build a relationship between 2 tables in the RelationShip window. Is the query important to be created

re: Ms Access 2003 Table and Form Query

The relationship works. It doesn't allow me to add data if the Client Id isnot there in Client Contact table.

My question is in the form how do i automatically display the company name and the address in their relavant field when i enter client id in the form which is valid.

re: Ms Access 2003 Table and Form Query

Let's do this one step at a time. Otherwise we will both stay confused all the time.

Yes. It is necessary to create the query first. This will be what the form uses instead of either table.

re: Ms Access 2003 Table and Form Query

ok.

I tried that and yes in the client id it is displaying me the value which i add in the client contact. But now i want is to add the company name and address automatically when i select a Client id in the form.

re: Ms Access 2003 Table and Form Query

Hi Neopa.

I am very confused.

In the Client Contact table i have kept Contact id as primary key and linked it with the Contact id of the billing table.

When i enter 2 invoices for the same contact id it doesnt allow saying that it cannot be repeated.

Please how do i design my table structure and query

Waiting for your reply.

re: Ms Access 2003 Table and Form Query

First, sorry for the delay responding. Things have been particularly busy these last few days.

This inability to enter multiple items is directly related to the designs of your tables. As you haven't shared that with us, I cannot comment on what you may have done wrong (or in a way that doesn't match your requirements).

In the mean-time, Normalisation and Table structures may be worth a read.

re: Ms Access 2003 Table and Form Query

Thank you for your response. Thats fine that you were busy. infact i m thankful that you are atleast replyin me, appreciated mate.

Please suggest

I want to create a billing database with the following field. Please suggest how many tables i need to create and what relationship in order to get multiple items.

Currently i have created two tables as below.

TABLE 1

Party Code,
company name
Contact person,
Address,
ph
fax
mob
email
website

Table 2
Invoice no
Order No
Challan NO
Invoice Date
Order Date
Challan Date
Party Code (Which will be linked to Table 1)
Company Name (Which will be linked to Table1)
Address ( Which will be linked to Table1)
SrNo
Description of Goods
Rate
Qty

What i want is for a particular invoice no/order no/challan no i want multiple serial no to be added as i add a row in the form.

Hope this is much clear than the earlier one.

re: Ms Access 2003 Table and Form Query

I think we can proceed from here :)

Before I start though, can you indicate to me how well you understood the article linked in my prior post. It makes a difference to how I explain things you see.

re: Ms Access 2003 Table and Form Query

Thank you for sending me the article link as that helped me a lot re-design my database structure and also helped to understand the structuring of a database. I came to know that what i had adopted was wrong process and which the article did mention.

While reading the article I have now designed on paper 6 tables as below:

CUSTOMER TABLE
Client Id - Primary Key
Company Name
Address
Ph
Fax
Mob
Email
Website

ORDER TABLE
Order No - Primary Key
Client Id - Foreign Key
Order date

ORDER DETAILS TABLE
Order Details Id - Primary Key
Order No - Foreign Key
Product Id - Foreign Key
Qty

PRODUCT TABLE
Product Id - Primary Key
Client Id - Foreign Key
Description
Rate

INVOICE TABLE
Invoice No - Primary Key
Client Id - Foreign Key
Invoice Date

INVOICE DETAILS TABLE
Invoice Details Id - Primary Key
Invoice No - Foreign Key
Client Id - Foreign Key

Please let me know if I am going in the right direction now and the next step to follow.

re: Ms Access 2003 Table and Form Query

Your [Product] table doesn't need or want a [Client Id] field.
Your [Invoice Details] table doesn't need a [Client Id] field either. This is already available via its link to the [Invoice] table.

The next step would be to rephrase the question relative to the new tables you have. That shows what you understand and helps me to avoid getting confused with multiple names for the same ideas.

re: Ms Access 2003 Table and Form Query

So now i did the changes as you suggested. How do i proceed further.

i.e. i want qty and Rate = Amount in the invoice.

What queries will be there on this.

re: Ms Access 2003 Table and Form Query

I would suggest this.