X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Yes/no Generated Report And Counting, Access 2016    
post Jul 11 2018, 05:59 AM

Posts: 2
Joined: 11-July 18

Good afternoon!

I am new on this forum, as well as a bit older "beginner" in MS Access. smile.gif

I would like to consult you something about my database, that I am building in my firm. I just posted an example, because currently database is pretty full.

I have in one table all trucks and the other table is equipment that is already in truck. Some equipment trucks have, some no (YES/NO).
And now, I want to make some report to know which equipment the truck is missing (NO), as well as how many "Hammers" i need to buy (to count equipment that is missing).
I am trying to make this by using Forms, but my knowledge is limited at this point.

I hope, that I explain this correctly, not so complicated. smile.gif

Looking forward to some advice. Thanx.
Attached File(s)
Attached File  Transport_and_logistic.zip ( 73.5K )Number of downloads: 2
Go to the top of the page
post Jul 11 2018, 06:55 AM

Posts: 891
Joined: 25-April 14

It seems you would not have any NO's.
The tTruckEquip table would only have items that exist, and you would query for items that are missing.
for your existing yes/no method:
Q1 would query the NO items
Q2 would count these NO items for all trucks.

Both queries would be in 1 report as subReports.
Go to the top of the page
post Jul 11 2018, 07:15 AM

UtterAccess VIP
Posts: 7,827
Joined: 24-May 10
From: Downeast Maine

You would do well to read about relational database design. This link includes other links. Crystal's tutorial is a good starting place.

The problem with the database as shown is that you are storing data in field names (Hammer, etc.). What if you add another piece of equipment? You would have to redesign forms, queries, tables, and reports. Better is to have a separate equipment table of equipment.

I have put together a quick sample to show what I mean. Each truck has many types of equipment, and each type of equipment is associated with many trucks. This is a many-to-many relationship, which needs another table (tblTruckEquip in the attachment) to resolve the relationship. Take a look at frmTrucks, with the subform fsubTruckEquip.

The attachment is just a sketch showing the basic design. It does not address additional considerations such as making sure each truck has all required equipment. That can be done, but it is important to understand the fundamentals first, and to have a solid design that will support what you need to do now and in the future.

Other considerations may include that a piece of equipment will no longer be required some day, and so forth, but one thing at a time.
Attached File(s)
Attached File  Transport_and_logistic.zip ( 46.82K )Number of downloads: 8
Go to the top of the page
post Jul 16 2018, 07:34 AM

Posts: 2
Joined: 11-July 18

Thank you very much. I am going to read a bit about this issue and to adapt table according to your instruction.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd February 2019 - 03:47 AM