Complex Report Sorting (2 Viewers)

[TClark14]

I am having trouble getting the sort I need for a report. I am probably overthinking it or maybe should go about this in an entirely different way. My table contains well names with drilling rigs assigned. Some, but not all will have 2 rigs assigned. Some rigs may the 1st rig on one well and second rig on another well. Some rigs may only be second rig on job. I need to print a report of the schedule. Prior to this all wells had only one rig and the report worked out great, but with the assignment of second rigs - I am confused. Both rigs are associated with a well and on the report I need the well to show up twice if it has 2 rigs - once with the Rig1 sort and again on the rig2 sort if hit has a rig2. So the sort would be all Rig1 first (alpha by rig) then by sequence. After rig1's will start Rig2's, again sorting rig alpha, then by sequence. I sure hope this makes sense to somebody.

I have attached a sample of the table I have and the report layout I am looking for.

THANKS!

 

[TClark14]

I am so confused - I realized I told you wrong on the sort I need - I was able to do that report with a subreport, but what they want is ahown an the revised attached document. Sorry about that, I have messed with this too long.

 

[Rabbie]

Firstly your table is not normalised. This is why you are having problems sorting your data. Read up on Data Normalization. It will make your job easier.

See this link for more info

 

[Simon_MT]

If we assume that a rig can only operate on one well at a time look at it form a rig point of view:

Rig + Seq is attached to Well

Well Table
Rig Table
Rig Allocation table

Well Table
Well
Well Desc

Rig Table
Rig
Rig Name

Rig Allocation
Rig
Well
Seq

Simon

 

[TClark14]

Thanks for your reply - that does make sense to me. I Always knew this database was put together wrong, but it was created many years ago by someone else, probably in the first version of Access that came out. I have inherited this monster and managed so far to keep it running, but knew it needed changed. We started using Access 2007 in the past couple of weeks and I figured now was the time to get it right. I have worked on it some but it needs a major overhaul and I'm not sure how to go about doing that when it is used several different people and it changes everyday. As it is, there is a huge table of wells with 90-100 fields for each well. I could make a copy of it and work separately but worried about getting all the data back into the correct tables once I have it completed. The fact that I only know what I have learned by trial and error and this website doesn't help. Any suggestions on where I should start?

 

[Simon_MT]

Create a new Backend database:

Add Tables

Fields Table
Field
Desc

Wells Table
Rigs Table
Rigs Allocation

You may need to add into Fields other sites such Survey sites and drydocks?

Create a Front End Database

Link the Tables from the Backend Database
Link the Tables from the Old Database

Populate the Tables

Fields
Wells
Rigs

Then sort out the Rig Allocation in two sweeps one first Rig and then on the second.

How it all pans out is another matter but the one advantage is that you know exactly what you what out of your system.

The Rig day rates must be rather good at the moment.

Simon

 

[DALeffler]

I agree completely that the data needs to be normalized. But in an effort to keep the current db you have functional until the data can be normalized, you might be interested in the following.

I copied the data from the word table into an Access table called "WellRigs" like so:

ID	WellName	RigName1	RigSeq1	RigName2	RigSeq2
1	XYZ		Myrig		1	AnotherRig	2
2	SMITH		Myrig		3	HisRig		2
3	French		Myrig		2		
4	Green		HisRig		1	MyRig		1
5	Grass		HisRig		2	AnotherRig	1
6	Sky		MyRig		4	HisRig		1
7	Flower					MyRig		2

Then created the following union query:

SELECT WellRigs.RigName1 AS RigName, WellRigs.WellName, WellRigs.RigSeq1 AS RigSeq, 1 AS RigNum
FROM WellRigs
WHERE (((WellRigs.RigSeq1)>0));
UNION SELECT WellRigs.RigName2 AS RigName, WellRigs.WellName, WellRigs.RigSeq2 AS RigSeq, 2 AS RigNum
FROM WellRigs
WHERE (((WellRigs.RigSeq2)>0))
ORDER BY [rigname], [rignum], [rigseq], [wellname];

Running the above query creates this output:

RigName		WellName	RigSeq	RigNum
AnotherRig	Grass		1	2
AnotherRig	XYZ		2	2
HisRig		Green		1	1
HisRig		Grass		2	1
HisRig		Sky		1	2
HisRig		SMITH		2	2
Myrig		XYZ		1	1
Myrig		French		2	1
Myrig		SMITH		3	1
MyRig		Sky		4	1
MyRig		Green		1	2
MyRig		Flower		2	2

Note the first two records of the query do not match the output expected in the Word download - is that a problem?

If you use the union query output for a report, remember to invoke the View > Sorting and Grouping in the report design to get the report to sort and group the same way the union query does.

If this just confuses the issue, ignore it...

hth,

 

[TClark14]

I have been out of town an am just now getting back to this. i am going to give this a try for the short term while I try to clean up the mess in the original db. Thanks for all the help and I'll post back after I have a chance to work with this.

 

[TClark14]

I am getting the following error when I run the qry:

Syntax Error (missing operator) in qry expression 'WellRigs.WellName'.

 

[TClark14]

Okay - I got the last problem worked out. It looks like this is going to do the job for now. I have another question. I have two fields that will be included in this query Program Year and District. I need the query to Prompt for these two fields. How can I add this to the code?

Thanks so much for your help!!

 

[DALeffler]

To get the query to ask for parameters, I modified the test data table ("WellRigs") to this:

ID	WellName	RigName1	RigSeq1	RigName2	RigSeq2	PrgmYr	District
1	XYZ		Myrig		1	AnotherRig	2	1984	a
2	SMITH		Myrig		3	HisRig		2	1984	b
3	French		Myrig		2				1984	a
4	Green		HisRig		1	MyRig		1	1984	b
5	Grass		HisRig		2	AnotherRig	1	1985	c
6	Sky		MyRig		4	HisRig		1	1985	c
7	Flower					MyRig		2	1985	c

and changed the union query to this:

SELECT WellRigs.RigName1 AS RigName, WellRigs.WellName, WellRigs.RigSeq1 AS RigSeq, 1 AS RigNum
FROM WellRigs
WHERE (((WellRigs.RigSeq1)>0) AND ((WellRigs.PrgmYr)=ParamYear)) AND ((WellRigs.District = ParamDistrict));
UNION SELECT WellRigs.RigName2 AS RigName, WellRigs.WellName, WellRigs.RigSeq2 AS RigSeq, 2 AS RigNum
FROM WellRigs
WHERE (((WellRigs.RigSeq2)>0) AND ((WellRigs.PrgmYr)=ParamYear)) AND ((WellRigs.District = ParamDistrict))
ORDER BY [rigname], [rignum], [rigseq], [wellname];

hth & welcome home & Happy Normalizing ,

 

[TClark14]

Perfect - thank you!

 

[TClark14]

I have a count on this report that counts the number of wells, the number of permits out, etc. Is there a way to get this to not count duplicates (the second time the well shows up for RIG2)

THANKS!

 

[DALeffler]

Create a text box in the detail section of the report, name it "RecordCount".

Set the control source to "=IIf([rignum]=1,1,0)"

Set the Visible property to "No".

Set the Running Sum property to "Over All".

Creat another text box in the footer of the report.

Set the control source of this text box to "=RecordCount"

Run the report.

Lemme know if that works for you!

Doug.

 

[TClark14]

Yes this works. It is counting the number of records for only Rig1 which I need, but I also need for this to look at a specific field, such out permitsout, and count the number of populated fields (not blank) only for Rig 1. Can this be done as well?

Thanks, as always!

 

[DALeffler]

You can use the same method to look at other fields.

If you had a field for "permitsout", try this:

Create a text box in the detail section of the report, name it "PermitsOutCount".

Set the control source to "=IIf(IsNull([permitsout]),0,1)"

Set the Visible property to "No".

Set the Running Sum property to "Over All".

Creat another text box in the footer of the report.

Set the control source of this text box to "=PermitsOutsCount"

Run the report.

Do you need help with the Iif statements?

hth,

 

[TClark14]

Yes, I have been able to get it to count "permitsout", but not able to get it to count "permitsout" for only RigNum=1

 

[DALeffler]

Have you tried "=IIf(IsNull([RigName1]),0,Iif(IsNull([permitsout)],0,1))" for the control source for the permitsoutcount?

 

[TClark14]

I can't get this to work - it says the expression has too few arguments. I probably didn't explain what I need very well. I have a field called RigNum created by the Union qry above. The following is what I need, but I can't get the expression written correctly.

I need something like this:
If RigNum = 1, then count PermitsOut (if permitsout isn't blank), If Rignum=2 then do nothing

I hope this makes sense.

Thanks!

 

[DALeffler]

First, the Union query has to include the "permitsout" field in the output.

Then in the not visible textbox in the detail section of the report, try the following as the control source:


=Iif(RigNum = 1, Iif(IsNull([permitsout]) = True, 0 ,1),0)

If that don't get it, I'll need to see a sampling of the data...

hth,