[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Multi Table MS Access Query

Posted on 2013-06-04
5
Medium Priority
?
113 Views
Last Modified: 2016-04-01
I have a MS Access database with three (3) tables:

- tbl_Master (primary key on "customer_id).  
1:Many relationship on "account_number" in the "tbl_Relationship table.

- tbl_Accounts (primary key on "account_number")
1:Many relationship on "customer_id" in the "tbl_Relationship table.

- tbl_Relationship (primary key on "customer_id" and "account_number")

I am trying to design a query (or two (2) queries that will show me the following:

QUERY 1
CUSTOMER_ID | TOTAL # of Accounts

I would like this query to show me the total number of accounts associated with each unique customer ID and just show the total.

QUERY 2
CUSTOMER_ID | ACCOUNT_NUMBER | UPDATE_BY | DATE

I would like the query to show me me the account numbers that are associated with each unique customer ID, along with who updated the account and when.

Can anyone help?
0
Comment
Question by:davidkohne
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
MrC63 earned 1000 total points
ID: 39220512
For the first query, use:

Select tbl_Master.Customer_ID, Count (tbl_Accounts.Customer_ID) as Total
From tbl_Master
Inner Join tbl_Accounts on tblMaster.Customer_ID=tbl_Accounts.Customer_ID
Group By Customer_ID
0
 
LVL 4

Expert Comment

by:MrC63
ID: 39220517
For query #2, use:

Select Distinct Customer_ID, Account_Number,Update_By,Date
From tbl_Master
Inner Join tbl_Accounts on tblMaster.Customer_ID=tbl_Accounts.Customer_ID
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 39220520
Query 1:

SELECT customer_id,count(*) Total_Accounts
  FROM tbl_Relationship
GROUP BY customer_id

Query 2:

select tbl_Accounts.customer_id,tbl_Relationship.account_number,updated_by,updated_date
  from tbl_Relationship
  inner join tbl_Accounts on tbl_Relationship.customer_id = tbl_Accounts.customer_id
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question