Approach to problem...

[vbaInet]

If there's no (i.e. direct or indirect) link between the tables then you're either attempting the impossible, the request from your client is illogical, or your tables aren't properly normalised.

Let's see the Relationships diagram.

 

[zozew]

Maybe i confused you earlier when i had a image of a report with the escorts names in red...that was a photoshoped image to show you the result i wanted....sorry

Here is the relations diagram:

Im not sure how i would do the relation between the Escorts table and the InmateProfile one.... as i explained earlier the ralation is made when the user pickes escorts for each branch on a specific date in the schedForm we discussed earlier.....

 

[vbaInet]

Well, in that case I will go with my third deduction... "tables not properly normalized".

You need a (junction) table to store this information. What if they wanted to know which Escorts were assigned to a BR two weeks ago? They can't because it wasn't stored anywhere. A report relies on records from a table or a query. So you need to devise a junction table between tblInmateCases and tblEscorts.

 

[zozew]

ok well that makes sense, so either the BranchID or the date could be linked, probably the BranchID is best. Ill try to structure make a new table save the data and link it..... and get back with it working...hahaha had to say it it felt good just saying it

thx again.....

 

[vbaInet]

Probably all three fields in a new table where BranchID and EscortID are foreign keys. I'm not sure but you understand your tables better so come up with something we can work with... or better still, a working solution

 

[zozew]

ok i think this is ok....now the data is linked to the tblInmateHearings....
So i was thinking when the user makes the selections of escorts for a specific date in the schedForm it is written to the three new tables i made. And if i manage to add the tblHearingDateEscort.HearingDateID to all records in the tblInmateHearings with the specific date from tblHearingDateEscort.HearingDate then the relations should work....right?

Im just not sure how i would build the SQL string to do that loop through all the records in tblInmateHearings and if my structure would work....?

 

[vbaInet]

Good try, but that's totally wrong I'm afraid.

Look at your tables and relationships in post #22 and compare it to your report in post #3 and see what fields you require to get that result. It is evident that you need the BranchID, EscortID and CaseID. From tblInmateCases you can get information from pretty much any other linked tables. So what am I saying to you? A link (or juncton) table between tblInmateCases and EscortID will suffice.

But you need to clear this up - why don't you have a Branches table? Why do you have BranchesName in tblInmateCases and not BranchID?

 

[zozew]

Good try, but that's totally wrong I'm afraid.

Sorry.....

It is evident that you need the BranchID, EscortID and CaseID

Yep i get that...but still i dont get how to link the EscortId really...

So what am I saying to you? A link (or juncton) table between tblInmateCases and EscortID will suffice.

Link or a junction...im trying to think of ways to do that but how....I think i get what you are suggesting...but how...
So the tblInmateCases.BranchID i link to the Link table and then I link the tblEscortsName.EscortID to the same Link table, but how will that work?With multiple escorts per branch etc...?

But you need to clear this up - why don't you have a Branches table? Why do you have BranchesName(changed name to Branch now) in tblInmateCases and not BranchID?

Well....im displaying the Branch name (combobox) not the Id in the InmateCases form (displayed as a spreadsheet) on the MainForm. The branches is just a number/text combo like "RTC 19" (there are 21 different Branches)...and the escorts assigned for each branch changes every time there is a scheduled hearing... So i dont really understand why i should have a Branches table..?

When i layed out the info i had to make tables. The Inmate and the CaseNumber was the...how do you refer to it... the key properties..? each inmate has a case/s each case is in one branch and each case has multiple hearingdates.....so the Branch table for me seems obsolete. I could have based the InmateCases table on Branch instead but as the CaseNumber seems more relevant i chose InmateCases instead of InmateBranches But maybe you are referring to how to save the schedule data..not sure?

Updated:
I created a look-up table (tblBranches) for the field Branch (combobox) in the form for the tblInmateCases where you chose the branch now...but i believe its not what you meant right?? I did it just to clean up things a little...

So i got these tables now...and the tblBranchEscorts is the one i think you would call the link table...but how and what fields....?

 

[vbaInet]

No need to be sorry, it's a learning process

The relationships:

tblBranches (BranchID) --> tblInmateCases (Branch)
tblCrimes (ID) --> tblInmateCases (CrimeID)
tblInmateCases (Branch) --> tblBranchEscorts (Branch)
tblEscortsNames (EscortID) --> tblBranchEscorts (EscortID)

See how you get on with the above info.

Regarding why you need a Branches table, it's a step of normalization. You have a certain number of branches and if those branches are currently being typed there's room for error if there's no place it's looking up the value from. Even if you can type all the values as a Field List in a combo box, it's still not an effecient way of doing things. It allows for flexibility and gives the added benefit of Indexing.

 

[zozew]

Ok, i kinda get what you are suggesting...

So used my old sheduleQuery as a base i added the two new tables to get the EscortsName field into the Query and it looks like this:

And running it i get nothing...which is expected....Because the tblBranchEscorts is empty.

So i need to populate that table with data from my scheduleForm...? but then doesnt that table also need the selected Date (dateForSchedule from the schedForm) to be complete, and how would i save the multiple escorts in that table....as a string of EscortsID separated with commas??

Or

if i remove that table (tblBranchEscorts ) i get all the inmates with a hearing that date (dateForSchedule from the schedForm) duplicated for each EscortName in the tblEscortName, maybe i should put in a criteria in the tblEscortName to loop the info in the schedForm....but then i guess it wouldnt be saved so the user can check on it at a later date...?


Any thoughts....below is the SQL string

SELECT DISTINCT tblInmatesProfile.MiddleName, tblInmatesProfile.FirstName, tblInmatesProfile.LastName, tblInmateCases.Branch, tblInmateHearings.Hearings, tblEscortsName.EscortNames, tblInmateCases.CaseNo, tblInmatesProfile.CellLocation
FROM (tblInmatesProfile INNER JOIN (tblInmateCases INNER JOIN tblInmateHearings ON tblInmateCases.CaseNumberID = tblInmateHearings.CaseNumberID) ON tblInmatesProfile.InmateID = tblInmateCases.InmateID) INNER JOIN (tblEscortsName INNER JOIN tblBranchEscorts ON tblEscortsName.EscortID = tblBranchEscorts.EscortID) ON tblInmateCases.Branch = tblBranchEscorts.Branch
WHERE (((tblInmateHearings.Hearings)=[Forms]![scheduleChooserForm]![dateForSchedule]));

 

[vbaInet]

You need to link those tables in the Relationships window and not in the query and enforce referential integrity with Cascade Update, that is if you've not already done this.

You mentioned in posts #4 and #5 that if you select a date the related BRs get displayed. If this is the case then whatever field that is I would imagine exists in tblInmateCases, so you can use that field. In what table does this date field exist?

Basically, for the report, BranchID, CaseID, InmateID will come from tblInmateCases. The inmates Surname and Forename will come from the related tblInmatesProfile table. EscortID will come from tblBranchEscorts whilst the Escort's name will come from the related tblEscortsName table. If this is set up correctly when you apply the filter it will display only those EscortIDs and BranchIDs.

The report will be grouped and sorted by BranchID, sorted by Inmates Surname/FirstName.

 

[zozew]

You need to link those tables in the Relationships window and not in the query and enforce referential integrity with Cascade Update, that is if you've not already done this.

I've tried but there is some unique identifiers missing so i cant check the referential integrity boxes...

You mentioned in posts #4 and #5 that if you select a date the related BRs get displayed. If this is the case then whatever field that is I would imagine exists in tblInmateCases, so you can use that field. In what table does this date field exist?

Its in tblInmateHearings...

Basically, for the report, BranchID, CaseID, InmateID will come from tblInmateCases. The inmates Surname and Forename will come from the related tblInmatesProfile table. EscortID will come from tblBranchEscorts whilst the Escort's name will come from the related tblEscortsName table. If this is set up correctly when you apply the filter it will display only those EscortIDs and BranchIDs.

Im sorry but i still dont see how the users selected Escorts will be added to the report this way...i know im a bit thick but doesnt the data in SceduleChooserForm have to be saved in the tblBranchEscorts...?

The report will be grouped and sorted by BranchID, sorted by Inmates Surname/FirstName.

The report is grouped that way...

Below is a link to the db as it is now, if you click the schedule report in the inmateProfile form you can choose the dates jan16 and 17 for some results, only have records with hearings those dates...

It might be easier if you see for your self what mess ive done?

http://www.anniblue.com/master_v16_share.zip

ps. the old strange behaviour still happens sometimes regarding the promt for the caseNumberID...but disregard that, if that happens just close the inmateProfiles form and reopen it it usually works then

 

[vbaInet]

See attached. I've given you more than enough code and information for you to go on.

On looking at your db, I realised that the layout of your report is flawed. The escorts pertain to the group of inmates, not to each inmate.

ps. the old strange behaviour still happens sometimes regarding the promt for the caseNumberID...but disregard that, if that happens just close the inmateProfiles form and reopen it it usually works then

The database you attached is not the same db that I worked on to eradicate this problem.

 

[zozew]

See attached. I've given you more than enough code and information for you to go on.

Thx ill do my best...i check it asap..

On looking at your db, I realised that the layout of your report is flawed. The escorts pertain to the group of inmates, not to each inmate.

Yeah the escorts are grouped per hearingdate and then sub group per branch

The database you attached is not the same db that I worked on to eradicate this problem.

i did copy all changes to my current db but then a similar problem came saying there was an invalid or something like it on an onCurrent event...so i started to outcomment somethings and made it work again...but i think most of your changes are there....i think...

 

[zozew]

Well im not sure what to say except thank you, not that im the least ungrateful but you did to much for me The concat function you made is perfect.

To be honest trying to code in a language im new to i feel so handicapped. Simple things like building a function to call from anywhere or Trying to figure out why a certain SQL string has a syntax error or similar, slows everything down to a crawl. Looking at the code you've added makes me smile as it is just as i imagined it, but differs distinctly from what i was doing....yours works and even looks good.

Thanks for taking the time and showing me how to get where one want to in Access and now especially VBA in access.

Im the type that learns by seeing bits and pieces of code and how it is related to other objects and methods etc and from that make my own functions.

This piece with the Escorts in the report was my elephant, you were totally correct, that the layout was flawed having an escort field after each row. I was naive in thinking i could somehow filter and group it after i got the data in to the report that's due to my lack in Access experience and general understanding of the structure, what you can and can't do. Having a string with comma separated values was a thought i had in my first sketch report i uploaded, but i thought later it looked better having the names displayed one on each row. Now i know better

Anyway thank you so so so much you did more then i ever expected. Please let me know if you ever need anything done in flash graphical or developed, as that is my forte.

Don't worry this is not good bye im sure ill be back but not regarding this at all for it works perfect, i did do some minor modifications but only to what was displayed.

Thx Zozew

 

[vbaInet]

The Concat function was a copy and paste from Allen Browne's website. I would normally create my own just so it's optimum but Allen's function does the job in yours.

Notes:
* If you're going to save the records then you also want to save the date in that table.
* From memory, I think Cell also needs to be normalized. A separate table for Cells would make for an easier way to manage them. I noticed this because it was one of the fields I had to deal with, so there may be more similar fields.

Happy to hear you've finalised this part.

 

[zozew]

Hi again, I have a post in Queries about this issue but seen as you wrote the SQL for this i thought I'd ask you.

The SQL for my report looks like this:

SELECT tblBranches.Branch,tblInmateCases.CaseNo, tblBranchEscorts.Branch, tblInmatesProfile.InmateID, tblInmatesProfile.LastName, tblInmatesProfile.FirstName, tblInmatesProfile.MiddleName, tblInmatesProfile.CellLocation
FROM tblEscortsName INNER JOIN ((tblBranches INNER JOIN tblBranchEscorts ON tblBranches.BranchID = tblBranchEscorts.Branch) INNER JOIN (tblInmatesProfile INNER JOIN (tblInmateCases INNER JOIN tblInmateHearings ON tblInmateCases.CaseNumberID = tblInmateHearings.CaseNumberID) ON tblInmatesProfile.InmateID = tblInmateCases.InmateID) ON tblBranches.BranchID = tblInmateCases.Branch) ON tblEscortsName.EscortID = tblBranchEscorts.EscortID
WHERE (((tblInmateHearings.Hearings)=[Forms]![scheduleChooserForm]![dateForSchedule]));

The result(Report) i get is great if an inmate does not have more then one Case (CaseNo) per branch otherwise he is duplicated for that branch for as many cases as he has in that branch. I would like to limit the inmateID per Branch to 1. So even if he has 20 cases in that branch he only shows up once under the branch grouping.

I've managed to do that with just using SELECT DISTINCT but then i have to omit the CaseNo field as that one is unique for each row...I've gotten some feedback on using SELECT TOP in the WHERE clause to get the top for each group but im not sure how to modify the WHERE clause so it fits my current SQL string...

thx again