Neater query

[jk42]

When I was building my database, I had to take a lot of tables and turn them into junction tables since there could be so many records per project. For example:

On FormProjectDetails (where I enter in all the project details) I have SubFormStudyDesign, which can have 0 to 8 entries for one project. I thought that was great, until I ran a query and included the entries from SubFormStudyDesign. So when I want to see a certain type of project and i include the study design, I get that one project 11.01 six times! One for each entry of study design. I need to include study design in these queries, but is there a way to make it neater? Or to not have each project be a separate line? Or do I have to change design again? Does this make sense?

Thanks!

 

[plog]

In general, programming has many 'right' ways to implement solutions. One exception to that is structuring database tables. Excluding minor nits we like to pick with each other about naming conventions or datatypes, we usually agree that there is one right way to set up tables for a specific database.

That means there is a proper structure for your tables regardless of anything else downstream (''I want my forms to do this...", "I want my reports to look like...", "When this table gets updated this set of data should..."). All that stuff is irrelevant to your table structure.

So post your table names and their fields, a few records of sample data and we can help you structure it properly. Getting this right will save you headaches like the one you are experiencing now.

 

[jk42]

Thanks, Pat. I am so new to Access and I have not even begun to crack them yet. I have so much to learn. I am literally learning by trial and error and a bit of reading.

Plog, Here is an example:

TblStudyDesignChoices
-FKStudydesign
-Multicenter
-Multi employee
-Single term

TblProjectDetails
-Projectid
-111.11
-222.22
-333.33
-Projectname
-AAAAA
-BBBBB
-CCCCC
-Projectstartdate
-5.5.13
-4.5.13
-3.3.12

Linked to:

TblJunctionStudyDesign_ProjectID
-PKStudyDesignProjectID
-(Autonumber)
-FKProjectID
-FKStudydesign

From there, I ran queries, and from those queries made a subform from the junction table that is embedded into the project details form.

Literally, all of my junction tables are set up in this manner that links back to the project details table. So... I really hope this isn't too bad. For the most part it seems to be working out for me. I am actually about to have to make another junction table that links back to the project table in this same manner. My design has evolved a lot in that things that I had in one table have had to be broken down into junction tables. I'm really trying so hard to make sure I do this the right way!

Thanks!

 

[plog]

That's not enough information to go on, and I'm not sure I'm even reading it correctly. Does that mean you have a field called '111.11'? That's probably wrong.

Can you post the database itself?

 

[jk42]

Go figure that my last 6 months of work is wrong!

Sorry, when I typed this out I had tabbed so show that the numbers and letters were examples of items in the tables
1. TblStudyDesignChoices
(FKStudydesign-examples below)
-Multicenter
-Multi employee
-Single term

TblProjectDetails
(Projectid-examples of projectid below)
-111.11
-222.22
-333.33
(Projectname-examples of project name below)
-AAAAA
-BBBBB
-CCCCC
(Projectstartdate-examples of start dates below)
-5.5.13
-4.5.13
-3.3.12

Linked to:

TblJunctionStudyDesign_ProjectID
-PKStudyDesignProjectID
-(Autonumber)
-FKProjectID
-FKStudydesign

I would LOVE to post the database itself but it has too much actual client information in it. or can I just post the relationships? Thanks so much for all of the feedback and help. It's difficult building this from scratch with no help from anyone but you awesome people in the forum!

 

[jk42]

Hi!

I finally figured out how to post this. Thank you SO much for looking at this for me! I appreciate any and all feedback!

 

[jk42]

Client ID isn't unique because of how the company had it set up in our Excel sheet. A client ID looks like this:
ClientID-(ClientName)
111-(JK42-XXX)
111-(JK42-YYY)
111-(JK42-ZZZ)
112-(PatHartman-AAA)
112-(PatHartman-BBB)
112-(PatHartman-CCC)

So i made the number and letters the ID over auto number, because I thought that would work and make things easier than using auto number. But I probably also freaked out at the thought of doing it wrong and added auto number anyway.


"If client ID comes from another application and it is unique there, you can add a unique index. If it isn't unique in the feeding system, then add the name field also and make a compound unique index."<---Is that what I did by having the letters and numbers be the PK?


Other than that, do you see any major flaws in my set up? I literally have not shown/talked to anyone other than the awesome people in this forum about this set up, so I really appreciate feedback.

Thanks!

 

[jk42]

I'm sorry if I wasn't clear. I wasn't necessarily worried about the duplicates, per se. I wanted to make sure that the query was correct, which I believe you confirmed in an earlier post. (thanks!) I am just not educated enough in Access to know how to take that query, and turn it into a workable report for business development. In my perfect report, I'd be able to say to BD

Project 11.01: XXX,YYY,ZZZ,AAA,BBB

as opposed to how the query will return it as:
11.01 XXX
11.01 YYY
11.01 ZZZ
11.01 BBB
11.01 AAA

And I"m sure it's doable! I just am never sure! I plan on taking another Access class, but unfortunately will not be able to until the fall, so until then it's a lot of forum and trial and error!

Thanks so much!