Counting Records and printing them

[jcwillette]

I have a main form that has license plates with a subform that has the dates they were in our garage. I would like to print a report out with a variable that will count the number of records each license plate has and print out the ones that are over the variable.

So let's say I want to print out only those license plates that have over 6 times in the garage. I would have an input box that the user would insert a 6 and it would search the datatbase and print out the license plate and each day it was in the garage.

Can this be done and is it difficult? Any ideas or suggestions would be apprciated.

Thanks for any suggetions in advance.

JC Willette

 

[scalextric59]

Use a query like this one:

SELECT count(LicPlate) as CntLP, LicPlate FROM <your_table> GROUP BY LicPlate HAVING count(LicPlate)>6

 

[jcwillette]

I have taken a few programming languages in the late 70s, so it has been a long time for coding. Being relatively new to Access, I am not sure where to put that code or how to use it in the query wizard. Gentle guiding is needed.

Thanks

JC Willette

 

[dkinley]

Welcome aboard to the forums, Access and the learning cycle!

Even though most use the QBE (query builder), it ships with a nifty button so you can view the SQL code. Queries are posted here in SQL because they are written in SQL (and executed in VBA) or because it is difficult to post QBE style (its a GUI interface to query building). What scalextric59 was posting was a sample piece of code that you could copy and paste in SQL view and then swap out "LicPlate" and "your_table" with the names you have for your field and table. Then, for comfort, you could switch back over to QBE view to see it graphically before executing and testing.

Here are some links to get you a well-rounded overview. They give plenty of examples, how-to's, and tutorials to get you over the smaller hurdles.

http://fisher.osu.edu/~muhanna_1/837/MSAccess/
http://www.techonthenet.com/access/index.php
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.functionx.com/vbaccess/
http://www.lebans.com/

Note that some links are version specific; however, for basic Access (and VBA) programming there is not that much variation - emphasis on basic. However, it will show you some logic and approaches to regularly encountered problems and implementation strategy.

Good luck!

-dK

 

[jcwillette]

Thanks DK...I am in the process of reviewing all the links and hopefully learn somthing....and I hope I cn put them to use....

Thanks again!

JC

 

[jcwillette]

I have read, pondered and tried several things, but I guess I still don't get it. Your links are really good and I will use them in the future, but I can't seem to get my problem fixed.

These are the Mainform (InputForm) Fields:

VehicleID AutoNumber
License Text
State Text
Make Text
Model Text
Type Text
Color Text
Code 4 Yes/No Box


These are my Subform (DateSubform) Fields:

License Text
Informed Yes/No Box
Date Date/Time
Time Date/Time
Reason Text
Checkpoint Text
Level Text
Section Text
Comments Text


What I want to do is print the main record with all the subform records for any given mainform record that has X amount of subform records. The x would be replaceable by a box that the user could input their choice in. Let's say that record #4 has 6 subform records and records #1 to #3 have only 3 subform records. I would like to print out all the data for any mainform record that has 5 or more subrecords.

Any ideas or help would be appreciated. Thanks!

JC Willette

 

[dkinley]

Okay ... first a slight critique

If you have a related table, it is highly recommended you have a field linking those two tables. Now, you have 'license' as like fields but can you guarentee integrity between the two?

A common mode is to create something like .. MainTable: pkMainID (Autonumber) and ChildTable: pkChildID (Autonumber), fkMainID (Long) and let the primary key (pk) and foreign key (fk) provide the linking and let Access do all of the work.

In this manner, you can run your query on the child table and count the records that have the fkMainID that is equal to the current record's pkMainID. If the count is > x then viola - show me the main (and/or child) records.

The first link goes into relationship building some, but you might want to do searches on that and normalization. I also notice your field names 'Date' and 'Time' - these are reserved words and should be modified. Also, not that you have any but having spaces in field names is generally frowned upon but can be worked around with brackets in code.

Here are some other links that may be of interest ...

http://www.itu.dk/~slauesen/UID/AccessTutorial.pdf
http://www.mvps.org/access/general/gen0012.htm
http://www.allenbrowne.com/AppIssueBadWord.html

Hope you don't take any of this the wrong way, but it looks as if this isn't a database to be taken lightly and if it could expand in the future - it will be important to set the foundation correctly to prevent problems later on.

Hope this helps,

-dK

 

[jcwillette]

A common mode is to create something like .. MainTable: pkMainID (Autonumber) and ChildTable: pkChildID (Autonumber), fkMainID (Long) and let the primary key (pk) and foreign key (fk) provide the linking and let Access do all of the work.


WOW!!! I guess I need to revamp my database...only thing is ..can i do it and not lose my data....i have almost 4000 records in the main table and another 3600 in the sub table.....what are my cahances of executing those changes above withou messing up the database??? Please advise!!!


JC

 

[dkinley]

Wow .. if working correctly with the primary/foreign keys that you do have then it might be solid already.

The problem will be providing the foreign key for the subrecords. To implement a scheme of this nature, just add a new field to the table and give it a name (usually the table name) with ID at the end. Then set it to autonumber - save the table - and then set the field to primary key (right clicking on it). You can add "pk" to the front of the field name - its your decision - some people do and others don't.

Do the same for the child table. In addition add another field, the foreign key for the parent table and set it to Long. You can add 'fk' to the front of the name, again - it's up to you. Worse case, give it the same name or very similar name as the field in the primary key in the main table. The only reason I do the pk/fk bit is is if I have alot of tables, it just helps me later on if I am doing the linking manually - there is no guesswork.

Here is the problem. To update the fkID in the child table, you will need to provide the pkID from the main table in this field. If you are advanced enough to do update queries - it will take much of the work out. You could do a query on the child table, set the criteria for your existing link (e.g., license) and the update field to the new pkID. If not, then you are doing a manual one-to-one match until they are all filled in.

-dK

 

[jcwillette]

Still having trouble getting any info out of the database in any kind of workable print. I can print out all the recordswhen I use the wizard for reports....but i don't want all that data...just the records that have more than what the operator wants to print out...Say I want to print out all records that have 4 or more subrecords...I want the user to be able to input his choice into a popup and have access printo out their request. I have spent lots of time and have purchased 3 books and read them...but no ideas at this point....any help woud be appreciated...

Thanks

JC

 

[dkinley]

No problem. The report looks like crap (I let the wizard do it) just to show some output, but the >4 count of records is done in the underlying query.

Hope this helps,
-dK

 

[jcwillette]

Still having problems....i have looked over your files and am sending database to ya for a good look over...

ok got it to upload...as you can see i am still working on it...but you can play with it and see what you can come up with....

again i am working with 2003 and would like to have the user insert the number he wants the data to be limited to before he prints out the rptLicenseDateTable.

any ideas are appreciated.

JC

 

[dkinley]

No problem. This may help you out seeing it in the way you are used to seeing in your fields.

I created new tables and added the primary and foreign keys. I then ran an updated query to get the foreign keys populated. These new tables have the 'tbl' designator in front of them to distinguish.

You will notice in one of your tables you have a blank license field. This could cause metric errors in your reports and possibly impact the structure of your design. In the added tables, the primary keys and such are 'hidden' from the user. In this manner - Access does all of this work behind the scenes and ensures this level of integrity.

I also created a tblValueList to hold your make, models, types and colors per normalization standards. I didn't explore this into the other table, but to give you an idea (also for application of the State field). Once you get a ton of data - make/model - you might want to explore "cascading combo boxes" in order to optimize data entry. Think of it as selecting the make "Honda" and then the model combo box will only show values that correspond to Honda.

I made a new form with the designator frm so you can see how I tied all of this in. I made the report and query you were looking for. It will prompt you for the number of licenses - figured a prompt might be good for a day-to-day type of status. Is up to you. You can analyze and compare to the tutorials and sample links for final determination.

Anyhow, dissect it and take from it what you will. It should provide the bridge you need to correlate to the other links for application to your database.

Hope this helps.

-dK

 

[dkinley]

I just noticed you had 4000 records in the original. I am attaching a screenshot of how I did the update query. Granted my interface is 2007, this will work in 2003. I showed that I am using and Update query and how I set the relationship between the two tables I created.

Note that your saving grace will be that you thought it out logically and hooked each table through a common reference of the unique ID of the license. After you run it - it is advised to go through and spot check to make sure everything lines up right.

Note that I did this after I created the new fields to hold the data. You will need to create the pk first (autonumber) and save it. If you look at the table, it should have automatically filled in all the fields. Only then can you assign it as the primary key. The same for both tables. The foreign key in the child table can only be populated through the Update query as shown.

What it won't do is populate orphaned records - records that don't match. You can gauge this by looking at the total number of records in the child table and the query will tell you how many records will be updated. If the number is the same then you know all fields received something. You should do a manual verification by randomly picking records throughout the dataset to verify the procedure was correct.

Good luck!
-dK

 

[jcwillette]

that is a lot of information....to take in and process....this will take some time...but it looks like you have put me on the right trail...

you have the input for how many times they have been to the garage and have listed them someplace.....is that a table you creat each time???? as i am looking to place that data into a report to display the license and each days data such as the date and time of each visit.....this way we can look at the data and decide if this is a guest of the hotel or a worker....

i want to extend a huge thank you for your time and interest in my project and you have been more than helpful...i have learned a lot in the past few days and it looks like the project is coming together in leaps and bounds....i had to input the 4000 records into the program within the last 10 days and it has been back breaking....along with creating the forms, reports, and queries to get the data out....thanks again.

 

[dkinley]

Ah yes, unfortunately the taking time bit for the learning curve - even though the curve never ends.

you have the input for how many times they have been to the garage and have listed them someplace.....is that a table you creat each time????

No, it's not a table per se - a recordset - but I get your question, but yes it's created each time based on the query I created and bound to the report.

No problem - it's important to try and get it as close to right as you can to prevent problems down the road. Unfortunately, that means the to invest in the time upfront amassing all the information you can. Again, hopefully this 'stare and compare' to the samples and tutorials will expedite your curve.

Good luck with your project!
-dK

 

[jcwillette]

DK, hav pondered and tried to foloow your examples....i can get the new database, query, and reports up and running....but when i do the update to in the query....my database falls out of alphabical order.....

can you advise me on how to keep it alphaetically sound?

Thanks...I have the next 2 days off to rework this project and I like the way it is looking....I am impressed with the professional look and feel....thanks for all your help......

JC

 

[boblarson]

but when i do the update to in the query....my database falls out of alphabical order.....

Are you talking about a table being out of alphabetical order or a query? Tables aren't necessarily stored in any particular order and they are actually only displayed by a system query if you are viewing or using as a table. If you have certain, specific, sorting that must occur then you need to apply that in a query.