Record count of all related tables...possible? (1 Viewer)

[theKruser]

I have a table that has multiple related tables. I am trying to display (in a from) the total record count of all related tables per record in the main table, but I do not know how to build the query...or if I should be using VBA. For example suppose:

tblCompany

-CompanyID (PK)
-ParentCompanyID (used for sections within the company, using CompanyID as FK)
-CompanySectionName​

tblPayable

-PayableID
-Data1 (etc, etc)​

tblRecievable

-RecievableID
-Data1 (etc, etc)​

Desired Result (simulated table with "|" as delimiter):

CompanyName | Payable | Recievable

CompanyA | Total # Payable | Total # Receivable
-Section1 | Payable | Receivable
-Section2 | Payable | Receivable

CompanyB | Total # Payable | Total # Receivable
-Section1 | Payable | Receivable
-Section2 | Payable | Receivable

Any help would be greatly appreciated

 

[pbaldy]

You could do it on the form with a DCount(), which would probably be easiest if there is only one company displayed at a time. If more, that could be a performance hog. You could also create a totals query that counted by customer, and join that query to the main table in another. You could use VBA, but I probably wouldn't. You'd only want to do it if the form was in single form view, and you'd use the current event.

 

[vbaInet]

Following one of Paul's suggestions (i.e. creating a totals query), here's a link on how:

http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx

One more way would be to do it in a report - Group on Company ID and sum in the group footer.

 

[theKruser]

Thank you for your reply. What I am looking for is a "Status Page" for display of information only. I want this to be a dynamic page, so when a new Company is added, it automatically displays.

Can you please throw a quick example of how you would go about this? I will tailor it to my needs, but I am just not sure how to even begin setting it up with DCount() in a query.

Thanks again for your help.

 

[theKruser]

If at all possible, can you please help me build a crosstab query? That would be the best solution to my issue...just not sure how to accomplish it.

Thanks.

 

[vbaInet]

Have you even attempted doing what was nicely explained in the link provided? Or explored the other ideas given by Paul?

 

[theKruser]

Yes...I tried both. Couldn't get it to work. I just wound up creating two queries for each table, one that pulled date and PK and the other that called a function to limit responces then count PKs. I then created a query to tie company to each count query. Wound up with 49 queries, but that was the only was I could figure it out.

(That's why I asked for an example)

 

[pbaldy]

You asked for an example without providing one yourself to build on. You expect someone to build a database from scratch that matches yours, populate it with test data, and create this process? I assumed you were asking vbaInet anyway, since that was the most recent response. I got out of the way.

 

[theKruser]

My intention was not to be abrasive.

I supposed I should have articulated my question a bit better. What I was asking was just for an example of the code structure one would use to achieve the desired result. i don't want anyone to have to build anything at all. I have found that I learn more if I ask for an example of the code then try to hone it as required.

I am certain there are many that just want people here to solve their problem for them. I fail to take that into account from time to time. My request, albeit clear in my mind, was not properly verbalized with the acts/requests/wants of others in mind.

 

[vbaInet]

I assumed you were asking vbaInet anyway, since that was the most recent response. I got out of the way.

Paul, you know that I value your input always so please don't feel I have hijacked your thread when I post a reply

@theKruser: What we would like to see is your effort and then we can guide you into fixing the problem. The link is quite easy to follow. You don't need a crosstab query.

 

[theKruser]

@vbaInet

I agree, the link is very basic and easy to follow. I understand how to create a totals query and how to tie one query to another for elimination processes.

I was just under the assumption that my goal would be easier to accomplish than the way I accomplished it. All-in-all, I now have the data I need, and I agree that a crosstab was not needed. It was just me thinking through the problem at hand. I thought that would be the best route simply due to the amount of tables I needed to join.

It is no big deal now. What I have might not be the best way, but it works.

Thank you for your time and help.

 

[vbaInet]

As long as you're happy and it works