Duplicates needed on the same line

[jgferg]

I am hoping some one can point in the right direction. I have two fields one called Ward and the other strpollLoc. There can be different Wards within the same polling location. For example Ward 1 & 2 can both have the polling location of My Middle School. And to throw a wrinkle in not every ward has a duplicate. I need my report to look like this

Ward Polling Location
1/2 My Middle School Two wards, one polling location
3 Your Middle School One Ward, one polling location
4/5/6 Any Middle School Three Wards, one polling location

So I need my report to look the above example, If it could be done in the query itself that would be fine also. Can anyone point me in the right direction Please. Thank you in advance for any help.

 

[BNPMike]

This is not easy in standard SQL because you are in effect comparing each record with the preceding one. If the location is unchanged add the Ward number to the end of the prior Ward number else leave this record as a new lineis what you want. SQL curiously does not have this function.

I suspect you are going to have to produce a recordset and then move through it record by record (ie with VBA)

 

[demoman]

I must be missing your problem. This does not seem to be a difficult problem.

Ward Location
1 SchoolA
2 SchoolB
3 SchoolC
4 SchoolC
5 SchoolC
6 SchoolA

So, Morman Wards 1 and 6, votes at School A, etc. You just associate each individual ward with each possible voting place. You cannot have a many-to-many link. If you see this relationship, create a transaction table.

 

[BNPMike]

jgferg's problem is he wants to see the output in a particular way. I must admit I support demoman's view that it is better to see if the package you are using can answer your requirements rather than your vision of the precise way it should do it.

The original request is quite difficult; demoman's solution is extremely easy (I would do Location to Ward, then if you put it in a report you could supress the multiple School C headings).

Is is worth solving the original problem? I doubt it.

 

[jgferg]

Thank you for all the help and responses. I truly appreciate it. I knew it was not going to be easy but hopefully I can come up with some kind of answer. Just to explain myself a little better. This is what I want my end result on a report. Going back to demoman's example on my report I want it to show up like this on the report
Ward Polling Location
1/6 School A
2 School B
3/4/5 School C
So each ward that has the same polling location would only be on one line with the forward slashes. I will try the things you suggested in the meantime, I don't think I explained myself very well and probally confused all of you, Sorry.

 

[demoman]

Yeh, I see that I misunderstood the problem (not the first time). You can get the desired output, but it takes a little work. I have done things like this in the past.

OVERVIEW: Using the same data structure I previously described, you will:

1) create a report that does not have any DETAIL section.

2) you need concantenate the Ward numbers and print them on a breakfooter (School) section.

3) you will probably need a counter to determine WHICH ROW you are on (within the School group), otherwise I do not immediately see a way to get the "/"'s inserted correctly.

Anyway, I'm sure you can get this to work with some trial and error. The Access report writer is rather weak in this area. If I remember when I last did this, I will send you an example.

 

[BNPMike]

jg

You actually explained yourself very well.

Many questions on these forums are like this: My door at home has a knob on the right. In my new home the knob is on the left. How can I make it on the right?. You then get two types of response. The first is apply a large metal plate to the ceiling and wear magnetic boots - you can now approach the door upside down and it will seem as if the knob was on the right. The other type of response (mine I confess) is just accept the knob has moved, open the door and move on...

If your life depends on having Wardx/Wardy/Wardz in that format then I guess you'll have to buy some magnetic boots...

 

[demoman]

Mike

I like your style!

Regards
Steve

 

[egapogi]

I will just respond to jgferg's need... don't worry about his table design.

*Paste this function in one of your module

Function udfGetWard(sSch As String) As String
On Error Resume Next
Dim cDB As Database, rs As Recordset

udfGetWard = ""
Set cDB = CurrentDb
Set rs = cDB.OpenRecordset("SELECT * from Table1 WHERE School = '" & sSch & "'", dbOpenSnapshot)
Do Until rs.EOF Or rs.BOF
udfGetWard = udfGetWard & rs!Ward & "/"
rs.MoveNext
Loop
udfGetWard = Left(udfGetWard, Len(udfGetWard) - 1) 'remove the last /
rs.Close
Set rs = Nothing
Set cDB = Nothing
End Function

*Create a Query Using this SQL Statement

SELECT DISTINCT udfGetWard(Table1.School) AS Wrd, Table1.School
FROM Table1

----
The above query should return something like this if demoman's record is used:

1/6 School A
2 School B
3/4/5 School C

*This method is a bit slow when you are accessing a table with huge number of records, but I am sure this will work.
If that is the case. you can make it just a bit faster, but not that really fast.

*Save a query with this statement and lets call it Qry1

SELECT DISTINCT School FROM Qry1

*And The final Query will be:

SELECT udfGetWard(Qry1.School) AS Wrd, Qry1.School
FROM Qry1

*Sounds the same.. Just let the distinct be handled first to minimize the procedure call.


HTH,
ega