How to make form?

[Marco Simone]

Hi all,

I am beginner in access and I have problems with making form.
I have 4 tables in database.
1 and 2 table are in relationship "one to many", and 3 and 4 table also in
relationship "one to many".
All tables have primary keys. I have put autonumber primary key for every
table.
I have trouble with making form for entering data into tables.
Can I put on one form fields from tables that are not in a relationship? I
would like to have one form with text boxes to enter data in all 4 tables.
Otherwise I would have 2 forms with subforms, so I dont know how to collect
same data for printing report.

Thanks for your answer.

 

[John Vinson]

Hi all,

I am beginner in access and I have problems with making form.
I have 4 tables in database.
1 and 2 table are in relationship "one to many", and 3 and 4 table also in
relationship "one to many".

How, if at all, are Tables 1 and 2 related to Tables 3 and 4?

All tables have primary keys. I have put autonumber primary key for every
table.
I have trouble with making form for entering data into tables.
Can I put on one form fields from tables that are not in a relationship? I
would like to have one form with text boxes to enter data in all 4 tables.
Otherwise I would have 2 forms with subforms, so I dont know how to collect
same data for printing report.

Typically one would use a Form for the "one" side of a relationship,
wiht a Subform for the "many". But if there is no relationship between
(say) Table1 and Table3, I don't know what it would mean to have both
tables on the same form! Say you had 318 records in Table1, and 225
records in Table3, each with their associated records in Tables 2 and
4; when you bring up record 100 in Table1, what would you want to see
on the Table3 part of the form?

I think you need to step back and consider the logical structure of
your tables FIRST before you start building forms and reports. What
real-life entities (persons, things, events) do these tables
represent? What, in the real world, is the relationship between them?

 

[Marco Simone]

I guess I don't understand well relationships between tables and how many
tables should I made.
It is simple database for payment based on a form for payment, when company
A pays invoice to company B.
Form for payment has this subjects(fields):
Company Name (Debtor) Table 1
City (Debtor) Table 1
Address (Debtor) Table 1
Model Number (Debtor) (field from 1 to 99) Table 2 (model number is
same for debtor and creditor, so no need for 2 fields)
Account Number (Debtor) Table 3
Calling Number (Debtor) Table 3
Amount (in currency)
Company Name (Creditor) Table 4
City (Creditor) Table 4
Address (Creditor) Table 4
Model Number (Creditor) (field from 1 to 99) Table 2
Account Number (Creditor) Table 5
Calling Number (Creditor) Table 5
Date of payment
Payment Code

Do you have any suggestions for making tables? I think I didn't made well.

Thank you for your help.
Marco

 

[John Vinson]

I guess I don't understand well relationships between tables and how many
tables should I made.
It is simple database for payment based on a form for payment, when company
A pays invoice to company B.
Form for payment has this subjects(fields):
Company Name (Debtor) Table 1
City (Debtor) Table 1
Address (Debtor) Table 1
Model Number (Debtor) (field from 1 to 99) Table 2 (model number is
same for debtor and creditor, so no need for 2 fields)
Account Number (Debtor) Table 3
Calling Number (Debtor) Table 3
Amount (in currency)
Company Name (Creditor) Table 4
City (Creditor) Table 4
Address (Creditor) Table 4
Model Number (Creditor) (field from 1 to 99) Table 2
Account Number (Creditor) Table 5
Calling Number (Creditor) Table 5
Date of payment
Payment Code

Do you have any suggestions for making tables? I think I didn't made well.

No. You didn't.

The way to determine the structure of Tables is to identify your
"Entities" - real-life persons, things, or events. Some of the
Entities I see here might be:

Companies (a company might be a creditor, or it might be a debtor, or
it might be both)
Models
Accounts
Payments
(perhaps a table for Loans)

Each of these Entities should have its own table; the fields in that
table should pertain *ONLY* to that entity - for instance, a Company
table would not have any information about Amount, just information
about the company as a company.

Without knowing the nature of your business beyond the fieldnames
you've posted I hesitate to post in any more detail... but I'd suggest
you do some reading about relational database design before you get in
too deep!

 

[Marco Simone]

Thanks John.
I have read one good book about access, but I don't have experience making
databases. I think I have made solution for my database, although it doesn't
go with "normalisation rules" for databases.
I have made this tables:
TABLE 1 (Company Name (Debtor), City (Debtor), Address (Debtor))
TABLE 2 (Company Name (Creditor), City (Creditor), Address (Creditor))
TABLE 3 (Model Number (Debtor), Account Number (Debtor), Calling Number
(Debtor), Amount, Model Number (Creditor), Account Number (Creditor),
Calling Number (Creditor), Date of payment, Payment Code)
Table 1 and Table 2 has relationship "one to many"
Table 2 and Table 3 has relationship "one to many"
This way I have main form based on Table 1, with subform based on Table 2.
Subform also has its subform based on table 3.
On first form are shown all forms. This suits my needs, because this
database is made just for printing, not for reports and history of payments.
I will try to study Northwind sample database and general template
databases, so that I have "hands on experience".

Marco

 

[John Vinson]

Thanks John.
I have read one good book about access, but I don't have experience making
databases. I think I have made solution for my database, although it doesn't
go with "normalisation rules" for databases.
I have made this tables:
TABLE 1 (Company Name (Debtor), City (Debtor), Address (Debtor))
TABLE 2 (Company Name (Creditor), City (Creditor), Address (Creditor))

So if you have a company which is a creditor in one circumstance, and
a debtor in another circumstance, you need to enter the company name
twice? What if you accidentally enter different addresses for the same
company in the two records?

The state of being a degtor or a creditor is *NOT* (in my opinion) a
valid attribute of a Company; and it's certainly not an
entity-defining attribute. A company *is a company*, whether it's a
debtor, a creditor, neither or both!

Secondly, if you do go with this model, what's the Primary Key of your
table? Do you allow for the possibility that two different companies
might have the same name? There must be hundreds of beauty parlors
named "The Mane Event" for example. Shouldn't these tables have some
sort of unique ID?

TABLE 3 (Model Number (Debtor), Account Number (Debtor), Calling Number
(Debtor), Amount, Model Number (Creditor), Account Number (Creditor),
Calling Number (Creditor), Date of payment, Payment Code)
Table 1 and Table 2 has relationship "one to many"

So each Debtor can have many Creditors, but a given Creditor can only
loan money to a single Debtor? That would not be typical of business
in my experience. And if so, what is the Foreign Key in Table2 which
links Table1 to Table2?

Table 2 and Table 3 has relationship "one to many"

Since I don't know the meaning of your "model numbers" or "account
numbers" or "calling numbers" I really can't comment; but again -
what's the linking field? What's the foreign key, and what does it
link TO?

This way I have main form based on Table 1, with subform based on Table 2.
Subform also has its subform based on table 3.

It really sounds like you started the design of this project with the
Form. This is very like building a house starting with the framing and
siding, and then putting in the foundations as an afterthought... and
it won't work any better!

On first form are shown all forms. This suits my needs, because this
database is made just for printing, not for reports and history of payments.
I will try to study Northwind sample database and general template
databases, so that I have "hands on experience".

If you're going to use Access at all (if it's just for printing you
might want to do it in Word instead, for example), it will be vastly
easier if you use it *as a relational database*. If you have the
information stored in your tables in a logical structure, with the
appropriate entities and relationships, you'll find that you can
generate the report that initially prompted you to create the
database, and great flexibility in generating *other* reports that may
turn out to be even more useful. If you drive the design by forcing
the data to fit a single report, that single report is the only thing
you'll ever be able to do with the database (without tremendous
hassles at least).

 

[Marco Simone]

Every table has primary key (autonumber field). Is it ok to use autonumber
field? I could use combination of 2 fields as primary key.

So each Debtor can have many Creditors, but a given Creditor can only
loan money to a single Debtor? That would not be typical of business
in my experience. And if so, what is the Foreign Key in Table2 which
links Table1 to Table2?

Table 1 (Debtors) and Table 2 (Creditors) are in relationship "One to many".
So in first record I could have 1 debtor and many creditors of that debtor.
If I made table like this, in second record (next debtor) can I enter same
creditor as in first record? I don't know to make relationship "many to
many".
I will try to learn more about relationship as it is most important in
access.

Thanks, Marco

 

[John Vinson]

Every table has primary key (autonumber field). Is it ok to use autonumber
field? I could use combination of 2 fields as primary key.

Autonumbers are very commonly used but they are not essential, if you
have a good "natural" primary key. A good key is unique (necessary -
hence names don't make good keys), stable, and preferably short. If
you have two fields which meet these criteria by all means you can use
them. Autonumbers are good for "behind the scenes" use but not very
good if humans will see them because they will always have gaps.

Table 1 (Debtors) and Table 2 (Creditors) are in relationship "One to many".
So in first record I could have 1 debtor and many creditors of that debtor.
If I made table like this, in second record (next debtor) can I enter same
creditor as in first record? I don't know to make relationship "many to
many".

A Many to Many relationship (universal in any nontrivial database) is
always handled by *adding another table* to resolve the relationship.

Again, *I don't know your business* so I may be offbase here; but I'd
suggest using a Loans table, both as an entity in its own right and as
this "resolver" table between creditors and debtors. Consider the
following structure:

Companies
CompanyID <autonumber or some unique code>
CompanyName
Address1
Address2
City
<etc etc>

Loans
LoanNumber <unique manual or automatic key, NOT an autonumber>
CreditorID <link to Companies, who made the loan>
DebtorID <another link to Companies, who got the loan>
LoanDate
Amount
<other details about this loan>

This would allow any company to serve as the creditor or the debtor
for multiple loans, without repeating information about that company.

 

[Marco Simone]

Thanks John,

I see that I have to learn a lot.

Autonumbers are good for "behind the scenes" use but not very
good if humans will see them because they will always have gaps.

If I use I autonumber field I don't have to put it on form, because it will
increment automatically when I enter new record, right?

Again, *I don't know your business* so I may be offbase here; but I'd
suggest using a Loans table, both as an entity in its own right and as
this "resolver" table between creditors and debtors.

I didn't know how to link creditors and debtors. The type of business is not
like making loans (like in financial institutions). It is accounting
company, who is printing bills for other companies. So, when one company
take goods from other, and pays it in some period, like 30 days.

Consider the following structure:
Companies
CompanyID <autonumber or some unique code>
CompanyName
Address1
Address2
City
<etc etc>

Loans
LoanNumber <unique manual or automatic key, NOT an autonumber>
CreditorID <link to Companies, who made the loan>
DebtorID <another link to Companies, who got the loan>
LoanDate
Amount
<other details about this loan>

So there would be tables COMPANIES, LOANS, DEBTORS, CREDITORS, right?
I could use query to make entry in one form.

Best regards, Marco

 

[John Vinson]

Thanks John,

I see that I have to learn a lot.
If I use I autonumber field I don't have to put it on form, because it will
increment automatically when I enter new record, right?

That's correct.

I didn't know how to link creditors and debtors. The type of business is not
like making loans (like in financial institutions). It is accounting
company, who is printing bills for other companies. So, when one company
take goods from other, and pays it in some period, like 30 days.

Well... still, the transaction (of one company taking goods from the
other and incurring a debt) is a real-live event - a valid entity in
its own right. It's not a loan then, let's call it a debt, or a bill
(though I can imagine that one act of indebtedness might in practice
generate multiple bills, if the debt is arranged with multiple
payments or becomes overdue).

So there would be tables COMPANIES, LOANS, DEBTORS, CREDITORS, right?
I could use query to make entry in one form.

NO.

A Debtor *IS A COMPANY*.
A Creditor *IS A COMPANY*.

All the companies would have records in the Company table. All
information about the company, address and so on, would reside in the
Company table *and only in the company table*.

If it clarifies it for you, let's change the name of my suggested
Loans table to Debts, or to Bills. In either case, the transaction (of
incurring an indebtedness) is a single, real-life event; all such
events should be stored as records in this table of Debts. Some of the
pieces of information that you need to know about a Debt are the loan
number (again I would *not* use an autonumber, see below); the date it
was incurred; the amount; the terms, I presume; and the identities of
the two companies involved. The way you represent those identities is
simply by having a "Foreign Key" - the CompanyID - as a link to the
Companies table; in this case you have two foreign keys to the same
table (DebtorID and CreditorID).

On your Form you would presumably simply have two Combo Boxes based on
the Companies table, bound to the CreditorID field and the DebtorID
field. If a transaction involves companies new to the system, you
would need a separate Companies form, which you could pop up as needed
from the combo boxes' NotInList events.

 

[Marco Simone]

Huh, I am feeling stupid. One more question, I take your time, sorry.

Loans table is "resolver" table between creditors and debtors. CreditorID
and DebtorID are foreign keys in Loans table. In what table are they primary
keys, since they are not in companies table. I guess they have to be primary
keys somewhere.
Also, can it be combination of CreditorID and DebtorID (both fields) primary
key of Loans table.

Thanks in advance, Marco

 

[John Vinson]

Loans table is "resolver" table between creditors and debtors. CreditorID
and DebtorID are foreign keys in Loans table. In what table are they primary
keys, since they are not in companies table. I guess they have to be primary
keys somewhere.

You may be assuming that the field *NAME* must be the same in both
tables. Sorry I didn't make that clear!

DebtorID is linked to CompanyID in Companies. So is CreditorID.

Also, can it be combination of CreditorID and DebtorID (both fields) primary
key of Loans table.

If you wish to ensure that CompanyA can purchase something from
CompanyB *ONLY ONCE*. I'd assume that you will be billing multiple
transactions for each company, though. I'd use the LoanID as the
primary key and allow for multiple bills.