Which is better a recordset or a query 1

[Klepdog]

I have started re-writing an Access program we have. The table now has several checkboxes in it. I need to write code to look at each checkbox and filter out records that have one to several fields that are either checked yes or no depending on information required. There is also a requirement to count these records. Which would be better to use several queries or recordsets to process this data. Once I have this data would it be best to toss the information (i.e. numerous reuired totals, specific records that contain unique info(3 or 4 seperate fields)depending on checkboxes) into a temp table so I could call it up for a monthly report or move it directly to a report. If a temp table is suggested how would I transfer this data to a temp table. Any suggestions or ideas would greatly appreciated.

 

[dhookom]

IMO, don't send a recordset to do something that a query can do. I would try to avoid temp tables if at all possible. If the query is too complex or slow, you could use temp tables.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

The table now has several checkboxes in it
Have a look here:

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Klepdog]

Thanks for the tip. I think I need to clarify. The program that I am using contains so far at least 15 different tables. Most have a relationship with another table ,a couple do not have any relationships. The table I am working with now has a one to many relation ship with another table. I tried to keep the the fields down to a minimum in each table, but unfortuantly due to the type of info that is being processed into this table I had to use the above mentioned check boxes. Below is a sample of the fields that I am using. There is more fields than this including more checkboxes, but I wont clutter up this post.

ID = Auto Number Primary
MyDate = Date/Time
Line Number = Number
User Number = Text (AlphaNumeric)
Prime = Text
Time = Text
Emergency = Yes/No (First CheckBox)
Add = Yes/No (CheckBox)
Trans = Yes/No (CheckBox)
Remarks = Text
Cancel = Text
Abort = Yes/No (CheckBox)
Discrep = Text
TypeEquipment = Number
etc.....

My problem stems from needing to use several queries into a report, but the report wizard will not let me. I think that I would be able to accomplish this using VBA. So I am back to my original question. Should I use a recordset/recordsets to get my data for the report or use SQL queries. The data needed for the report is contained in this one table. I need to have the total adds, cancels (sorted and sub totaled for each reason), emergency (with the date of each and the Discrep) and so on... All of this must be filtered as the previous month and done by TypeEquipment. I really do not write program for a living, but my boss threw this at me and said get it done. Even for Dummy books would not help me.

 

[dhookom]

I would expect both PH and I would consider your table un-normalized. It's a bit difficult since you know what information you are storing and how you want to use it. We only see field names and have no idea about the content of your data.

You might be able to use reports with subreports. Normalizing union queries might be part of your solution.

If you provided some sample data and desired output in your report, we could provide greater assistance.

Duane
Hook'D on Access
MS Access MVP

 

[Klepdog]

I don't understand. "table un-normalized".

The program is at work and is on an intranet system. All I can try to do is give you samples of the data from memory. Most of the queries would be repeated for each type of equipment.

One querie would need to sort for the previous month. TypeEquipment =1, Emergency = -1, MyDate, Prime, and Discrep. The need report data would be total emergency, plus the MyDate, Prime, and Discrep for each emergency. I would need this for again for TypeEquipment = 2.

Another querie would be for Adds. This would be a total for all adds for TypeEquipment = 1 for the previous month. Again for TypeEquipment = 2.

Another querie would be for Cancels. Totals Cancels and subtotals for each reason for the previous month. Again one for each type of equipment.

One would be for total completed, but this one must be filtered by another field that only contains C,F,T, and blank and the canceled field. Again for each type of equipment.

And So On...

As you can see there is alot of queries that need to be processed for this report. It would have been easier if the report wizard or report design would have let me use all the queries I need to use.

 

[Klepdog]

I forgot the cancel field is a text containing the reason for the the cancel.

 

[Klepdog]

I think I understand the Normal vs Un-normal now. I would say that the database is un-normal. Unfortuantly I do not have all the information or the time to create tables with the appropriate data to make this a Normal database. Some of the database is set up Normal as often as I could make it, but the fact remains no. This particular table is un-normal. Some of the fields could be could come from another table but I did not set it up that way.

Even if this was normal database, I would still be looking at numerous queries for the monthly report and still in a bind on how to put it all together.

 

[dhookom]

Baby steps. Again "If you provided some sample data and desired output in your report, we could provide greater assistance."

It helps if you can provide some context to your data. What are you storing? What is the primary key? What events in your business cause you to add or edit or delete a record.

Duane
Hook'D on Access
MS Access MVP

 

[Klepdog]

Sorry, for taking so long to understand, but I do not know VBA except from what I have been able to put toghether.

The first field is of course is the ID field which is autonumbered and is the Primary Key.

The next is MyDate - DATE/TIME field. Formated to 2 digit day/3 digit month/ and a 2 digit year. This is the schedule date

LineNumber, a Number field. This is line number assigned the scheduled event to occurr that day.

Prime is a text field for the serial number of the equipment to be used. There is also a corresponding field for a spare.

Type Equipment is numerical field that tracks what equipment is being scheduled for use.

Time is a text field that proably should a date/time. This time of day for the schedule event. This field has to be in a 24hr format.

Add field is a Yes/No. This is used to establish if the line number was added after the posting of the original schedule.

Cancel is a text field. This is used to state the reason for the schedule line number to be canceled. If filled in it assumed the line is a cancel.

Abort is a Yes/No field. A Yes is checked if the Equipment item failed at the start of the event.

Emergency a Yes/No field. A Yes is checked if a emergency occurred during the event.

Remarks a text field. This is used to state any prestart information the customer requires for the event.

UserNumber a text field. This a alphanumeric field identifing the customer.

Discrep is a text field. This is used to describe a discrepancy associated with that equipment item for that line number to include the reason for a emergency.

This table is called tblSchedule, it is used only for the daily event schedule.

ID MyDate LineNumber Prime Spare TypeEquipment Timesched Add Cancel Abort Emergency Remarks UserNumber Discrep Status
1 16-Dec-11 1 159 154 1 1300 False False False 135 C
2 16-Dec-11 2 185 232 1 1415 True False True 195 O2 Flow Stopped
3 20-Dec-11 1 159 232 1 0800 False WX False False 010
4 20-Dec-11 2 354 N/A 2 0900 False True False 15120 C
5 21-Dec-11 1 185 159 1 1200 False False False 001G Track Linkage Worn C

I hope this make sense. Again this is not all of the fields

 

[dhookom]

It seems that you want to create totals based on specific fields in your table. Since these totals involve different fields and possibly different groupings, you may need to create subreports. However, you still haven't done a good job of stating based on the table, how you exactly want the numbers to appear in your report.

Duane
Hook'D on Access
MS Access MVP

 

[Klepdog]

For the most part this is correct. Totals for adds, cancels (grouped by reason), total scheduled vs total complete, total emergencies (plus date, prime, and discrep) etc... for each TypeEquipment. There is more totals and such but they are very, very similar to what I have shown you. The layout would of course be similar to this below:

For the Month of Month and Year)
For Equipment #1
Total Scheduled :
Total Complete :
Total Complete By Prime (SerialNumber):
Total Complete By User Number:
Total Adds :
Total Cancels :
[Broken down by reason (i.e. 1 WX)]
Total Emergencies:
[List each emergency including date, Prime (SerialNumber), and discrepancy]

Repeat for each equipment type

Another headache for me is that the personnel that create the schedule tend to add extra line numbers with dates but leave everything else blank (allowing them "pencil in adds" to the printed schedule before updating the program). These blank records need to be filtered out for the totals.

Don

 

[dhookom]

I think you can create report grouping on "Month of" and then "Equipment Type". In the Group Footer for Equipment Type, add text boxes with control sources like:
Count the number of records where linenumber is not null

=Sum(Abs(Not IsNull([LineNumber])))

Count the number of records where "complete"

=Sum(Abs([Expression that identifies completeness]))

Count the number of records where "Adds"

=Sum(Abs([Expression that identifies adds]))

If you need detail information from the schedule, place bound text boxes in the detail section.

Duane
Hook'D on Access
MS Access MVP

 

[Klepdog]

This worked for the report. One other question. If the prime aborted and the spare had to take the scheduled event, what would be best way to count the usage of the spare equipment item instead of the Prime when I total the usage for each serial number. Like the example below.

ID MyDate LineNumber Prime Spare TypeEquipment Timesched Add Cancel Abort Emergency Remarks UserNumber Discrep Status
4, 20-Dec-11, 2, 354, 252, 2, 0900, False, , True, False, 15120, C

252 actually was use in this line instead of 354 since Abort was true.

From a query that sorts by Prime, with a criteria of the serial number and includes Mydate, Abort. I am using = Count([Prime]) - Sum(Abs([Abort])) - Count([Cancel]) for the total usage for each serial number but this does not take in account for the serial number that was actually used instead of the prime.
Any suggestions?

Don

 

[dhookom]

I don't understand. I need significant fields only and more records. I would also like to know what you expect for output and why.

Duane
Hook'D on Access
MS Access MVP

 

[Klepdog]

The problem is simple, but I have absolutely no idea on the answer. If I had numerous schedule records with the [Prime] (i.e. the serial number) completing the schedule event [Status] "C". I would create a query that counts the [Prime], criteria being the serial number, and use the where clause on the [Status] field with "C" as the criteria.

But, in the table there is another field called [Spare] (i.e. the serial number for the spare equipment) and one called [Abort]. If I had several records where [Status] is "C", but the [Abort] is TRUE, the [Spare] would complete the event instead of the [Prime]. In this case, I would not be able to use the query I used earlier as it would have counted the record with the Abort for the Prime instead of for the Spare. What I am doing is the counting the actual usage of each serial number.

ID MyDate LineNumber Prime Spare TypeEquipment Timesched Add Cancel Abort Emergency Remarks UserNumber Discrep Status
4, 20-Dec-11, 2, 354, 252, 2, 0900, False, , True, False, 15120, C
16, 24-Dec-11, 4, 281, 148, 1, 1200, False, , True, False, 1582, C
89, 30-Dec-11, 2, 252, 403, 2, 0815, False, , True, False, 15120, C
103, 31-Dec-11, 3, 148, 142, 1, 0915, False, , True, False, 1582D, C


If I used the query I wrote above, Record #4 the serial number 354 would be counted for the completion of the schedule event instead of 252 which actually did. The same problem occurs for Records 16, 89, and 103. In this instance these records need to be counted against the serial number of the Spare.

I hope this explain this question better. Thanks
Don

 

[dhookom]

I would create a query of the non-abort only and then union the records with Spare. You would need to align the Prime and Spare columns into a single column.

SELECT "P" as PrimeSpare, Prime, ...
FROM ...
WHERE Status = "C" and Spare is Null
UNION ALL
SELECT "S", Spare, ....
FROM ...
WHERE Status = "C" AND Spare Is Not Null;

Duane
Hook'D on Access
MS Access MVP

 

[Klepdog]

That helped alot. It took me about an 1 and a 1/2 hours to get the SQL correct, but it's done. Thanks for the help, and sorry about the baby steps.

Don

 

[dhookom]

With more complex solutions, I prefer "baby steps" to make sure you understand and can accomplish less complex tasks one at a time.

Duane
Hook'D on Access
MS Access MVP