Results 1 to 14 of 14
  1. #1
    Narumon is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    6

    Acess Database - with monthly datas

    Hello,



    I have a specific task to do, and I think MS access is the way to go. Im coming from excel programming (VBA), but access is totally different.

    Im trying to implement a database to use it in our company - but first I would like to design it on paper, and I have a problem which I dont know how to solve.
    We have lots of printers which we are renting out to companies. The printers have lot of details in the databes (Brand, Type, SN number, IP Address, fees, etc), and the most important thing is, that how much page is printed with that particular printer every month. So to every printer, every month I have two numbers; mono and colour page count. (forexample: 2020 January printouts: 3111 mono page and 2343 colour page, 2020 February 1576 mono and 2678 colour printout, etc) and I need to track this for every printers (we have hundreds) and every month. What is the best way to do this? Make a table with 12 months on it and connect to printers? But what if we change year? Or what is the best method to do this?

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no, table fields needed:
    ------------
    RowID (auto)
    CoID
    Date
    PrinterSerial
    PageCount

    then you can query on any date range, any company, any printer.

    youd also need tables: tCompany, tPrinters

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I created a draft based on your post, but I don't know the level of detail you need.
    ranman has offered a simple, straightforward that may serve your requirement.

    Here is the draft that may offer some ideas if you need more. Please feel free to ignore the draft.

    Good luck with your project.
    Attached Thumbnails Attached Thumbnails PrinterMgmtDraft.PNG  

  4. #4
    Narumon is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    no, table fields needed:
    ------------
    RowID (auto)
    CoID
    Date
    PrinterSerial
    PageCount

    then you can query on any date range, any company, any printer.

    youd also need tables: tCompany, tPrinters
    HM I think I am still not understand.

    I have a table containing customer data
    CompanyID, ContractID, CompanyName, CompanyAddress, ContactPerson, ContactPhone, ContactEmail,

    I have a table containing the contract data
    ContractID, ContractNumber, ContractStartDate, ContractEndDate, ContractType, ContractMonthlyFee, MonoPageInMonthlyFee, ColorPageInMonthlyFee

    I have a table containing the printer data
    PrinterID, PrinterBrand, PrinterModell, PrinterSerialNumber, PrinterIPAddress, PrinterPhysicalAddress, MonoPageExtraFee, ColorPageExtraFee, MonoPageCount, ColorPageCount

    So I need to connect the Contracts with the customer. (There are customers who have more than one contracts). And I need to connect the printers with the contracts. Under one contract we have maybe 1 printer, but it can be 70 printer as well.

    And there is the monthly page readout problem. The printers send me how many pages the customer printed in that month with that particular printer. And this is important because we have free pages in the contract (MonoPageInMonthlyFee and ColorPageInMonthlyFee). And if they print more in that month, they need to pay the printer MonoPageExtraFee (which is a currency) and ColorPageExtraFee. (And if I put a MonoPageCount and a ColorPageCount row which is a sum of what the printer printed in its entire life, I need to calculate what was the monthly printout (PageCount-PreviousMonthPageCount but where can I store this?)
    So If I understand correctly you say that I need a plus row to the Printer data table which is just a date? How will this be good? If I just put a date in it like 01.2020 how will the program knows that in that month what was the color page count and what is needed to bill?

    Thank you for your help.

    Regards
    Zoltan

  5. #5
    Narumon is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    6
    Ah visually its make more sense - please read my last reply, it contains more information about what I would like to achieve.
    Thank you.

  6. #6
    Narumon is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    6
    Anyone? Please give me some ideas Im stuck

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you worked your data with the draft model?
    Here's another model, test your sample data against this draft and adjust accordingly.
    See this "stump the model" article.
    Attached Thumbnails Attached Thumbnails AnotherDraftPrintMgt.PNG  

  8. #8
    Narumon is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    6
    Quote Originally Posted by orange View Post
    Have you worked your data with the draft model?
    Here's another model, test your sample data against this draft and adjust accordingly.
    See this "stump the model" article.
    Yes Im trying to understand - and the first three draft is what Im understand but not the ProdID - this last one is how to connect with the printers? How Can I put data in it according to the specific printer? This is what I dont understand. Thanks

    update: Maybe I need to connect with the tPrinter and not with the ContractID?

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    Narumon is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    6
    Quote Originally Posted by orange View Post
    Click image for larger version. 

Name:	DraftPrinterContactProduction.PNG 
Views:	38 
Size:	24.6 KB 
ID:	40634

    Try this one with your test data. Let us know what doesn't work.
    Okay I built it this way and put soma data on it. Still not shure how do I add more printers to one customer.

    I'll uploaded my database here - so you can see the structure. Any suggestion? (I can make a query with the wizard but how do I get the company I want? - It list me the First company only)

    https://drive.google.com/open?id=1eM...UOF5WddiIXRiLL

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    With the excellent advice from Orange, I was able to create a form/subform arrangement.
    Your requirements are centered around Contracts, so I made that the main form with printers as the subform.
    The customer is selected for the contract via a combobox on the main form.

    tusz-davegri-v01.zip

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with the setup by davegri. Also, when you are checking for which printer(s) can be assigned to a Contract, you will need logic to identify those printers that are NOT currently on a Contract - AND -if there are no available printers (all are assigned to current contracts) then you must account for that. You can not use the Printers table as an "available printer list" since some are not available.

    Also specifications re print volumes and colors etc would be part of Contract.
    Good luck with your project.

  13. #13
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I've worked on this project a bit more and have expanded it's scope. Here's the Main Menu:
    Click image for larger version. 

Name:	mm.png 
Views:	17 
Size:	15.7 KB 
ID:	40778

    And here's an example of the Contracts form:
    Click image for larger version. 

Name:	contracts.png 
Views:	17 
Size:	33.7 KB 
ID:	40779

    And here's the complete DB:

    tusz-davegri-v03.zip

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is by no means any where close to being complete, but it is a little different way to look ad the data.

    I changed a couple of tables - here is the relationship window and some questions. (I would think about using tblUsage instead of Datum.)

    Click image for larger version. 

Name:	Design1.png 
Views:	13 
Size:	110.9 KB 
ID:	40795

    and here is the dB
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. The best way to store datas in access database
    By atzdgreat in forum Access
    Replies: 5
    Last Post: 09-15-2019, 06:07 AM
  2. Replies: 17
    Last Post: 07-05-2018, 03:30 PM
  3. Replies: 1
    Last Post: 06-13-2013, 09:23 PM
  4. Unable to open the database in Acess 2007
    By Bhat59 in forum Access
    Replies: 1
    Last Post: 08-09-2012, 07:38 PM
  5. Sending emails from Acess Database
    By Lirizarry in forum Access
    Replies: 3
    Last Post: 01-26-2012, 10:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums