Creating a Customer Rollup table

[ChuckW]

Hi,

I have one table in my database called tblTransactions which has fields such
as TrxDate, CustomerName, ProductNumber, InvoiceDate, Qty and Amount. I
created a form that has a combo box that does a lookup of all companies in
the tblTransactions table. Using distinct, it lists all companies. Once
selected, the user can click on a report based on a query that gives all
transactions for that particular customer in the combo box. Most customers
have two accounts - a retail and a warehouse account. So an example of a
customer name would be "ABC Company - Retail" or "ABC Company - Warehouse".
Right now, the user can select one or the other and run the report. What I
want to do is to somehow create a customer roll up table that will list ABC
Company. The user can select this account and get all transactions for both
account (retail and warehouse).

Can someone help?

Thanks,

 

[Jerry Whittle]

You need two more tables, at least, to do this. You need a tblCustomer table
which lists all the needed info for the Customers. Next you need a
tblAccounts table to list all the customer accounts. You'll have to have at
least one account for each customer. Then you join table tblTransactions to
tblAccount to tblCustomer to get the list of Customers.

 

[ChuckW]

Jerry,

Thanks for your help. I can see creating the tblCustomer table. However, I
don't have account number in my data. In the tblCustomer table, I can create
a field called CustomerMain and also CustomerName. The CustomerMain can be
ABC Company listed twice and then for Customer Name ABC Company - Retail and
ABC Company - Warehouse. How would I proceed from there?

Thanks,

 

[Lord Kelvan]

i think what he is trying to say is you would have a table that would
store the accounts nto as a number but as the words Warehouse or
something

so in the table customers you will have the company ABC company and in
the accounts table you will have warehouse and another row for retail
and they woudl be linked together with a forigen key

so your tables woudl look like

customer
customerid customername
1 ABC company


Account
accountid accountname customerid
1 warehouse 1
2 retail 1


so as you can see the two rows in the account table are linked to the
row in the customer table

hope this calarifys it for you

 

[ChuckW]

Thanks for your help. I think I understand the need for two tables. For my
form, should I create a combo box based on a query that looks up the
Customers table still? Then somehow I need to have a query that will look up
customers based on the accounts table. I am a bit of a novice at this. Can
you give me some pointers on how to structure the queries to do this?

Thanks,