Building peer groups from a form?

I have financial and demographic information for ~10,000 companies for the last 6 years.  My goal is for a user to be able to make custom peer groups of companies for further analysis. It appears that the best way to be able to set up a form so that a user could choose options on a form and then run a query based on the selections made on the form. I'd like to first confirm that this is possible?Peer groups could be based on a few factors (State or states, Asset size, years of data available).  Currently, the data is in 6 tables, one for each year.  The 6 tables have the same number of fields. However, the number of records changes as companies are formed and go out of business.  Each table has the same primary key (Tax ID). Check boxes on the form would designate which factors to include or exclude.  The form would be the criteria for a make-table query.My big question at this point is whether this set up (6 tables of annual data) and the same field being the primary key is a workable one for the project I'm looking to do. I'm also wondering what type of relationships I should set up between the tables.  It's been almost ten years since I've last used Access so I'm trying to remember what it's capable of.
0
Utf
3/29/2007 7:48:03 PM
access 16762 articles. 2 followers. Follow

1 Replies
587 Views

Similar Articles

[PageSpeed] 55

Allen,
Thanks for your prompt response.  I think my database is of the "more 
involved structure" variety. Here's an update:

-Sadly, I might be forced to maintain the seperate table format because of 
the way the data comes in.
-I figured out how to create a query which lists all the unique TaxIDs from 
all the years, along with the Asset size from each year, and some expressions 
which return 1 or 0 if the company had assets > 0 (was in existence) for that 
particular year.
-I'm in the early stages of conceiving a form to toggle which groups of data 
to include in the query, which should probably output to a table of peers.

I'll keep you posted.  Thanks again for your help! I'll probably be back. 
Kevin

"Allen Browne" wrote:

> Well, I have to agree that's a big question, Kevin. :-)
> 
> A separate table for each year is not ideal. Better to have them all in one 
> table, with date fields to indicate when the company joined/left the group. 
> So you will have these 3 tables:
> - Company table, with CompanyID primary key
> 
> - PeerGroup table, with PeerGroupID primary key
> 
> - CompanyInPeerGroup table, with fields:
>     CompanyInPeerGroupID      AutoNumber (pk)
>     CompanyID         relates to Company.CompanyID
>     PeerGroupID       relates to PeerGroup.PeerGroupID
>     JoinDate             when the company was added to this group
>     DepartDate         when the company left this group
>                                 (Blank if still current.)
> 
> The interface will be a main form bound to the Company table, with a subform 
> bound to the CompanyInPeerGroup table. The subform will be in continuous 
> view, so you can add as many rows as you need. It will have a combo box for 
> PeerGroupID.
> 
> If you need a more involved structure, where companies can have different 
> kinds of grouping within them (BOM, employees, mailing lists), and the 
> groupings can contain both companies and persons, there's a sample database 
> illustrating how to do that here:
>     People in households and companies
> at:
>     http://allenbrowne.com/AppHuman.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Kevin M." <KevinM@discussions.microsoft.com> wrote in message
> news:142E634A-915E-4E65-86D1-7B5EBC3873A4@microsoft.com...
> >I have financial and demographic information for ~10,000 companies for the
> > last 6 years.  My goal is for a user to be able to make custom peer groups 
> > of
> > companies for further analysis. It appears that the best way to be able to
> > set up a form so that a user could choose options on a form and then run a
> > query based on the selections made on the form. I'd like to first confirm
> > that this is possible?
> >
> > Peer groups could be based on a few factors (State or states, Asset size,
> > years of data available).  Currently, the data is in 6 tables, one for 
> > each
> > year.  The 6 tables have the same number of fields. However, the number of
> > records changes as companies are formed and go out of business.  Each 
> > table
> > has the same primary key (Tax ID). Check boxes on the form would designate
> > which factors to include or exclude.  The form would be the criteria for a
> > make-table query.
> >
> > My big question at this point is whether this set up (6 tables of annual
> > data) and the same field being the primary key is a workable one for the
> > project I'm looking to do. I'm also wondering what type of relationships I
> > should set up between the tables.  It's been almost ten years since I've 
> > last
> > used Access so I'm trying to remember what it's capable of.
> > 
> 
> 
0
Utf
4/2/2007 8:32:03 PM
Reply:

Similar Artilces:

Listing in previous records in Form by vertical scrolling
Please help, I would like to set up a form the way that if I enter the record I would like to see that record and access should open a new line where I can add a new record. I would like to scroll vertically in the old record to edit them eventually. I know that there can be a Record selection but I don't want that. Thank you for help. I cannot find it myself. On Wed, 28 Nov 2007 07:40:01 -0800, Jan <Jan@discussions.microsoft.com> wrote: >Please help, > >I would like to set up a form the way that if I enter the record I would >like to see that record and access s...

building a worksheet row by row
I hope somebody can assist. My worksheet calculates rows of data, which I wish to slide into another worksheet and secure; then change the original data, slide those results under the first. Keep repeating. Is there a way to do this without manually pasting values each time? David Dave "Sliding" rows from one sheet to another would involve the use of VBA. See Ron de Bruin's site for moving/copying rows to next available empty rows. http://www.rondebruin.nl/copy1.htm Gord Dibben Excel MVP On Thu, 4 Nov 2004 14:04:03 -0800, "DaveButcher" <DaveButcher@discussio...

Opening a VBA form in outlook
I'm just getting my head around VBA in outlook, and have created a form from the VBA project API, within outlook (2000). I named it and saved the project. So now how do i open this form from inside Outlook ? I have looked at the list of forms in the forms list, for the various categories, Personal Form library, Standard Form library, etc, But i cannot see my newly created form . Any advice welcome. Gerry Abbott DISCLAIMER: This e-mail may contain proprietary information, and is covered by copyright. It may be legally privileged, and is for the intended recipient only. If you are not...

Moving Exchange groups to a different OU
Native Win2k3/Ex2k3 environment. We just discovered the hazards of moving groups like "Exchange Enterprise Servers" to a different AD folder/OU. Does anyone know if there's an MS article that lists ALL the different groups that cannot be moved (Exchange or otherwise)? We're trying to reorganize AD but don't want to make the same mistake twice. Thanks. The groups you don't want to move out of the Users container: - Exchange Domain Servers - Exchange Enterprise Servers. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.c...

Can I Build an Access Statement (in 2007) as a String and Execute
Hi, I know that I can build a SQL statement within a string and then execute it like the following: strSQL = "UPDATE tblAuditRpt " _ & "SET tblAuditRpt.AuditRptYesCount = " & lngInitialEvalCount & " " _ & "WHERE tblAuditRpt.AuditRptID=25;" CurrentDb.Execute strSQL, dbFailOnError Can I do the same with an Access statement itself? For example, How could I execute the following statement if I built it in a string? lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetai...

Setting up a booklist using an easy bibliographic entry form?
I am a librarian in a small independent school. I need to build an acquistions list from several faculty lists. I would like to type in the data from each list and make one list that can be alphabetized or arranged by vendor, publisher, author, title, etc. Make sure the Faculty Acquisition Lists are all in the same format. Copy & paste the lists into one document. Then select the entire table and use Data/Filter/Advanced selecting the Copy to a new location and Unique Items Only options. This will give you one unique list that can be sorted. If you have the teachers provide the...

Building Assembly
I'm trying to build an assembly that creates a directory that is named after an attribute in opportunities. this is what I have, and it is not compiling into a dll, any advice? public string CreateDirectory(string root,string folderName) { } Luke Davis, MCSE: Security DEM Networks - Senior Systems Architect 7225 N First, Suite 105 Fresno, CA 93720 Office: 1 (559) 439-1000 Fax: 1 (866) 640-2041 www.demnetworks.com ...

Data Forms #3
Hi I have a spreadsheet that I am filling in using a form. I have also a row at the bottom with blank rows in between with a running total. When I print there is a huge gap between the last entry in the form and the totals at the end. Is there a way that the form can enter the next line and push down the totals so that there are no gaps. This is what I have. Name Salary Tax Total Ann 42000 5000 37000 Joe 37500 6000 31500 Total 79500 11000 68500 This is what I want Name Salary Tax Total Ann 42000 5000 37000 Joe 37500 6000 31500 Total 79500 11000...

Distribution Group #4
I have a Distribution Group created in my Domain for the purpose of sending emails to members of the Group. Is it possible to create an external mailbox (doe@abc.net) within my domain so that emails sent to the Distribution Group will also be forwarded to this external mailbox in a different Domain. Thanks. ...

Import Windows Mail groups into Outlook 2007?
I successfully imported my Windows Mail (Vista) contacts, but the groups (distribution lists) didn't come over. Do I have to create them all over again? Yes. Well known. Groups in Windows Mail have no counterparts in Outlook and cannot be imported. Create Distribution Lists if you think you need them. Categories work better. -- Russ Valentine "Lynn" <Lynn@discussions.microsoft.com> wrote in message news:5EFD6CF4-4FCD-4110-A2D6-03FAC7C12209@microsoft.com... > I successfully imported my Windows Mail (Vista) contacts, but the groups > (distribution l...

Building Kits
I am having trouble using the build kits tab. I went through and made sure all the components were available to build the kits. I click the build tab and the program prompts that there is enough inventory to build 2 kits. I enter 2 and then hit the build tab and then ok. When I go to the properties screen the inventory qty is still 0. I copied the kit and gave it another name and did the same process and was able to build the kits the first and only time. Any suggestions? known bug, apply the latest hotfix and you will be good to go. "akpetshop" <akpetshop@discussions.mic...

mail group members not receiving emails
When I send an email to an internal email group two members of the group do not get the email that is sent, anything that I can try to fix this ? ...

Forms question
I have to create an Access program. Basically I need to create one table and form in which information namely school marks (total mark) will be entered. Second this information is then relayed to a second person who tallies up the subtests and enters another number in another total column probably on another form. At the end of the day this number should be the same as the number entered by the first person. If it's not we need to display an error message or warning. Please help. How can I accomplish this (2 forms, one main one sub linked on a query which will update it as it is ent...

Grouping by email domain
Hello all, I am using Outlook 2003, and would like to know if someone can help me with grouping my messages in a particular folder's view. What i would like to do is to be able to group the messages by the domain part of peoples email address, so for example by "hotmail.com", "verizon.net", etc.... Ive looked through the grouping fields, and i only see fields to group by which are the whole email address, including the part before the @ sign. Is there anyway in which i can set it to group only by the part of the email which is after the @ sign? Thanks in advance...

Opening client form with apostrophe in name
I have been trying to make sense of previous responses to similar questions without success - so my problem! I have a client form from which I open another form with additional information on a client. When I have a client with an apostrophe in the name (O'Neill) I get the message --------------------------- Syntax error (missing operator) in query expression '[Full Name]='John O'Neill''. The system generated code is stDocName = "Additional Details" stLinkCriteria = "[Full Name]=" & "'" & Me![Paramet...

A PCCharge Pro Group Started
I am a "semi-retired" PCCharge Pro Tech - I am willing to help the best I can... I know this software inside and out... I set up a Google Group called Ask A PCCharge Pro and a Yahoo! Group - a moderated forum: http://groups.yahoo.com/group/APCChargePro/ Join! A PCCharge Pro ...

Macro to build table
I have 6 questions that may be answered yes or no, and for the sake of keeping everything in integer form, let's assume "1" for yes and "0" for no. Depending on how a customer answers all 6 questions, they will receive a predetermined score. I am attempting to develop the answer key, so-to-speak, to refer the reps to once they are finished asking the customer these questions. I was hoping for the code that would build a macro to create this tabe for me - a table that would lay out all of the 6 question-answer combinations, like the following: Questi...

Processing Group Policy XML reports (Looking for feedback)
Hi all, I just recently have started a small series on using powershell to extract information out of the XML reports that the grouppolicy module produces. The first article is here: http://outputredirection.blogspot.com/2010/01/using-powershell-to-search-for-group.html I'd be really interested to get some feedback/tips/criticism/whatever from everyone here in the newsgroup, if any of you have time. If this kind of shameless self-plug is bad form, let me know & it shall not happen again :) -- v(^_^)~Clint http://outputredirection.blogspot.com In message <#u...

Excel form on website
Can I copy an Excel form onto my website and use it as part of a customer order form? Will site users be able to enter personal data onto the form? If so, how do I activate the form once it's been copied onto my web page? ...

Error when building payroll build
One of my users is continuously getting an error when clicking OK on Calculate Payroll Checks. The error is - The checks cannot be calculated. Errors were found when building the checks. All other users can build the exact same build with no issues. What could be the issue here? Jack-One thing you may want to check is compare the users security. One user may/may not have rights to benefits/deductions/batches, etc. "Jack Tundra" wrote: > One of my users is continuously getting an error when clicking OK on > Calculate Payroll Checks. The error is - The checks cannot b...

Can't add users in the distributio group
I manage one forest with 2 domains. The exchange is in domain abc.local in forest abc and domain ggg.local is in China in the same forest abc. Users in ggg.local can't be added to the distribution group in abc.local domain. I select the right domain but it can't find Contacts or Other Objects. When I am adding users from abc.local domain, I can see Users, Groups and Other objects. What is wrong here? I ran DOMAINPREP on the ggg.local domain and created RUS in for that domain. Can I create a distribution group in the ggg.local domain? Will it show in outlook? Also, newly created ...

Entourage Build Information
Processor: Power PC Is there a matrix of Entourage versions and their respective build numbers anyone can provide? This information would be of great use in troubleshooting client/server issues. Thanks in advance, Chris I haven't checked specifically for Entourage, but Wikipedia usually has such data published - you might check there. HTH |:>) Bob Jones [MVP] Office:Mac On 11/13/08 3:24 PM, in article 59b61e39.-1@webcrossing.caR9absDaxw, "chrisxmartinez@officeformac.com" <chrisxmartinez@officeformac.com> wrote: > Processor: Power PC > > Is there a ma...

Grouping footers with detailed info
I have a report that has 2 columns. My footer does not stay with the detailed info it goes to the nest column or page. I want the footer to say with the detailed info. I have tryed grouping together. Can anyone help? -- Lisa S. Lisa wrote: >I have a report that has 2 columns. My footer does not stay with the detailed >info it goes to the nest column or page. I want the footer to say with the >detailed info. I have tryed grouping together. Can anyone help? Is "my footer" the report footer section? If so, it should span across the page, not in a column. You can use ...

Query Groups
Is there a way for users to find out who are in the query groups? User can send a message to query group with delivery receipt. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "HenryG" <HenryG@discussions.microsoft.com> wrote in message news:1098E4B9-7310-45A7-9FEA-9AA1C2B7DA24@microsoft.com... > Is there a way for users to find out who are in the query groups? Unfortunately there is no way to see group membership for QBDL, because when e-mail is sent to it, exchange is going to go to GC (global catal...

Synchronising Personal Calendar with a Shared Group Calendar
I would like to be able to scynchronise my personal calendar in Outlook with a Shared Calendar so that it happens automatically and I dont have to update both calendars. Does anyone know how this is done within Outlook 2003? "Martin" <Martin@discussions.microsoft.com> wrote in message news:AFD497ED-929D-4765-A417-0C0126EEFC2B@microsoft.com... >I would like to be able to scynchronise my personal calendar in Outlook with > a Shared Calendar so that it happens automatically and I dont have to update > both calendars. Does anyone know how this is done within...