How to accomplish this ?

[pakerly]

Hello, I am wondering if it is relatively easy to accomplish this?:

I want to make a form with following into:
ACCOUNT NUMBER:______
Product:________
AMOUNT:______

I want to store the results in a table called productusers, that has:
ACCOUNT NUMBER
NAME
ADDRESS
CITY
STATE
ZIP
PRODUCT
AMOUNT

and I dont want to enter such things as NAME, ADDRESS, CITY, STATE,
ZIP on the form, but if they exist based on account number in another
table (users), i want to fill that info in both in the new table and
on the form when they hit tab or click in the product field...And
finally have an ADD button that puts all of this into a record in the
new productusers table

If the users account number does not exist in that other table or
there is no address info than dont transfer to this new table. How
would I do something like this?

 

[Allen Browne]

How are your clients conencted to the accounts?

Is it:
a) A client can have multiple accounts;
b) One account can have many clients (joint accounts);
c) Both (a) and (b), or
d) Any client can have only one account, and it's unique to that client.
From that, you can determine the tables you need to handle this data.

Any account will presumably have many transactions over time. (Not sure if
these are orders, shippings, sales, purchase orders, or what.) And each
transaction could have many line items. If you are not sure how to tie this
data together, I suggest you open the Northwind sample database that
installs with Access, and look at the relationships diagram. You will see
the connections between customers, products, orders, and order details.

In the Northwind database, any particular order defaults to the client's
usual address, but can be directed to another address if needed. If you look
at the Orders form, and the AfterUpdate event of the CustomerID combo, you
will see how the address is filled in automatically.

But the absolutely crucial thing is to get the tables right before you try
to build the interface (the forms.)

 

[pakerly]

I think this is simpler than you think.
Each account will have one record.
So for example I might have a record that looks like this, after I
enter this data into the form and add it to the new table:

111111,Paul Smith,1122 W 22 ST,Beckly,WV,12345,Forks,17.50

and if at a later time I enter this customer again, using the form,
and enter in 2.25 as AMOUNT, the record would change to:

111111,Paul Smith,1122 W 22 ST,Beckly,WV,12345,Forks,2.25

So only one record per account, and than when I have all my info
entered via the form and all records are in the table, I want to make
a report that will display some of the things in the table.

 

[Allen Browne]

No, that's not the way you build a database. There's too high a risk of
inconsistent data (e.g. 11111 is Paul Smith one record, Joan Fonda on
another row, and Barney Rubble next time) and too much repetitive data to
enter (address every row.)

What you have is a typical flat-file (spreadsheet like) table, but it's not
how you build a relational database. 'Normalization' is the technical term
for the science of how to build a relational structure. The first step
consist of looking at the data and deciding what belongs with what.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I think this is simpler than you think.
Each account will have one record.
So for example I might have a record that looks like this, after I
enter this data into the form and add it to the new table:

111111,Paul Smith,1122 W 22 ST,Beckly,WV,12345,Forks,17.50

and if at a later time I enter this customer again, using the form,
and enter in 2.25 as AMOUNT, the record would change to:

111111,Paul Smith,1122 W 22 ST,Beckly,WV,12345,Forks,2.25

So only one record per account, and than when I have all my info
entered via the form and all records are in the table, I want to make
a report that will display some of the things in the table.