468,253 Members | 1,300 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

Horizontal Report problem

Hi Guru's,
Hopefully I can explain this OK.....What I am trying to do is
create a QC check sheet using the following fields: ASN, PO & Qty. This
information will be at the top of each report with loads of labels
below which users will have as a check list.
1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
I have set it up so far so that a new page is forced for each ASN. On
each page there should be 1 ASN number, however many PO's that belong to
each ASN and the Qty of units for each PO.
The problem I am having is that I can't work out how to get a list of
PO's (horizontal) across the top. If I put the PO in the header, only
the first one will appear. If I put it in the detail section, the list
is vertical and goes down the page rather than across it which wont work for
this report.

Can this be done?

TIA
Mark
Nov 13 '05 #1
4 2171
I'm guessing that from the lack of replies that this is not possible? Could
someone please confirm my suspicions.

Cheers,

Mark

"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:FG***********@newsfe1-gui.ntli.net...
Hi Guru's,
Hopefully I can explain this OK.....What I am trying to do is
create a QC check sheet using the following fields: ASN, PO & Qty. This
information will be at the top of each report with loads of labels
below which users will have as a check list.
1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
I have set it up so far so that a new page is forced for each ASN. On
each page there should be 1 ASN number, however many PO's that belong to
each ASN and the Qty of units for each PO.
The problem I am having is that I can't work out how to get a list of
PO's (horizontal) across the top. If I put the PO in the header, only
the first one will appear. If I put it in the detail section, the list
is vertical and goes down the page rather than across it which wont work for this report.

Can this be done?

TIA
Mark

Nov 13 '05 #2
Ray
"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<FG***********@newsfe1-gui.ntli.net>...
Hi Guru's,
Hopefully I can explain this OK.....What I am trying to do is
create a QC check sheet using the following fields: ASN, PO & Qty. This
information will be at the top of each report with loads of labels
below which users will have as a check list.
1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
I have set it up so far so that a new page is forced for each ASN. On
each page there should be 1 ASN number, however many PO's that belong to
each ASN and the Qty of units for each PO.
The problem I am having is that I can't work out how to get a list of
PO's (horizontal) across the top. If I put the PO in the header, only
the first one will appear. If I put it in the detail section, the list
is vertical and goes down the page rather than across it which wont work for
this report.

Can this be done?

TIA
Mark


Hello Mark,

Perhaps what you could do is set up 2 reports, one is the main report
and the other will be a subreport.

1. Report one: will be the way you have it set up currently.

2. Report two: will be a sub report that is set up like a labels
report with horizontal label sorting, not vertical sorting. Then
add a subreport control to the main report in your detail section.
Set it to can grow: yes and can shrink: yes.

Make sure that the sub report record source has the ASN Number
so that the Child/Parent connection needed for the subreport
control can be linked.

You'll have to play with the subreport control and the subreport
itself to have all data displayed according to your needs.

Hopefully, this will get you started in the right direction. I have
used simular technics like this before and works well.

Regards,

Ray
Nov 13 '05 #3
"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<yU*************@newsfe1-gui.ntli.net>...
I'm guessing that from the lack of replies that this is not possible? Could
someone please confirm my suspicions.


If the subreport method doesn't work you could write a public string
function that when given the ASN opens a recordset to records with
that ASN and builds a string of all the PO's. The function can be
placed in the SQL string like:

GetPOList([ASN]) AS ListOfPOs

and then have a textbox on your Report with a ControlSource:
ListOfPOs. It's slow and not very pretty or even considered good
practice but I have tried this and it seems to work well when the
record count isn't huge.

Here's an example:

tblCustomerPurchases
CustID Long
theDate Date
Brand Text

Public Function GetBrandList(PurchaseDate As Date) As String
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String
Dim strTemp As String
Dim lngI As Long
Dim lngCount As Long

GetBrandList = ""
strTemp = ""
strSQL = "SELECT Brand FROM tblCustomerPurchases WHERE theDate = #" &
Format(PurchaseDate, "m/d/yy") & "#;"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount > 0 Then
MyRS.MoveLast
lngCount = MyRS.RecordCount
MyRS.MoveFirst
For lngI = 1 To lngCount
If lngI <> lngCount Then
strTemp = strTemp & MyRS("Brand") & ";"
MyRS.MoveNext
Else
strTemp = strTemp & MyRS("Brand")
End If
Next lngI
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
GetBrandList = strTemp
End Function
Then putting SELECT DISTINCT CustID, theDate, GetBrandList([theDate])
AS BrandList FROM tblCustomerPurchases GROUP BY CustID, theDate; as
the RecordSource of a Report allows me to drag BrandList from the
Field List into the Report Details section along with CustID and
theDate.

James A. Fortune
Nov 13 '05 #4
Hi all,
Thanks very much for your suggestions but I managed to get it working
before I had a change to check for new posts.

I did it by setting the 'vertical' property of all labels and text boxes to
true and building the report on it's side. When it's printed out, it looks
as though it was designed in landscape format. All the titles, etc were
placed in the ASN header with the PO text boxes in the detail section and it
worked a treat.

Hope this info helps other and thanks again,

Mark
"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:yU*************@newsfe1-gui.ntli.net...
I'm guessing that from the lack of replies that this is not possible? Could someone please confirm my suspicions.

Cheers,

Mark

"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:FG***********@newsfe1-gui.ntli.net...
Hi Guru's,
Hopefully I can explain this OK.....What I am trying to do is
create a QC check sheet using the following fields: ASN, PO & Qty. This
information will be at the top of each report with loads of labels
below which users will have as a check list.
1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
I have set it up so far so that a new page is forced for each ASN. On
each page there should be 1 ASN number, however many PO's that belong to
each ASN and the Qty of units for each PO.
The problem I am having is that I can't work out how to get a list of
PO's (horizontal) across the top. If I put the PO in the header, only
the first one will appear. If I put it in the detail section, the list
is vertical and goes down the page rather than across it which wont work

for
this report.

Can this be done?

TIA
Mark


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Lloyd Stevens | last post: by
4 posts views Thread by Trevor Best | last post: by
2 posts views Thread by drum2001 | last post: by
reply views Thread by Mr. Beck | last post: by
reply views Thread by selimzairi | last post: by
1 post views Thread by sushanta | last post: by
2 posts views Thread by seajays | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.