Results 1 to 4 of 4
  1. #1
    Bren is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    1

    Art Database from Excel Spreadsheet Help Please


    Hi, I started a spreadsheat in Excel and decided I needed to put this in Access because I needed to add photos of all the records. I transferred the spreadsheet into Access successfully and the table shows up. However, the table has too many columns of data. This is for an art collection inventory. I need to keep up with all the art purchased, improvement of the art, and art sales. Would I need to setup tables like this?? I will have several restoration items, frame, cleaning, appraisals, etc. for one painting. I will have a report that shows how much I have in each painting so I will know what to sell it for. Is this possible??
    Thanks for your help!! I am feeling a little lost, I have watched several normalization videos and I just don't get it. Table 1 is the spreadsheet I transferred with over 377 records.


    Table 1 Painting Table 2 Restoration Table 3 Sales
    ID ID ID
    Artist Title of Painting Title of Painting
    Title of Painting Date Date
    Cirra Vendor Sold To
    Size Item Price
    Medium Cost Profit/Loss
    Date Acquired
    Purchase Price
    Wholesale Price
    Price
    Current Location
    Notes
    Photo

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,604
    Restoration table makes sense. Don't repeat Title of Painting, just save PaintingID.

    I presume a painting is sold only once so Sales table not really required, just record sales info in Paintings table.

    Profit/Loss should be a calculation, not data input.

    Advise not to use spaces nor punctuation/special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,360
    not really enough information to provide a suggestion how to proceed but make the following observations

    1. for all tables, give your ID field a meaningful name - and since this should be a primary key include a PK suffix. e.g. RestorationPK
    2. for tables 2 and 3, you don't need Title of painting, instead have foreign key to link back to the PK of table1 - e.g. PaintingFK and you can look it up when required.
    3. Don't have spaces and none alphanumeric characters in field and table names e.g. sold to, profit/loss
    4. Don't use reserved words (Date)
    5. if painting can move around, consider having location (with date from and paintingFK) in a separate table
    6. photo - keep as a jpeg/whatever in a designated directory and store the path to the file in the table
    7. Size - consider splitting into two fields, width and height
    8. artist, medium, vendor, sold to - consider having in a separate lookup table and store the FK instead
    9 no need to store profit/loss, it can be calculated when required

  4. #4
    Join Date
    Apr 2017
    Posts
    1,131
    tblItems: ItemID, ItemTyp, ArtistID, ItemTitle, Cirra, Size, Medium, Notes, ItemStatus;
    (You can have several different types of items. E.g. ItemTyp = 1 for paintings, ItemTyp = 2 for statues, etc. The field ItemStatus indicates, is the item in stock, in restortion, or sold - ItemStatus 1, 2, or 0 respectively.)
    tblItemPhotos: ItemPhotoID, ItemID, PhotoDate, PhotoLink;
    (Don't store photos in database, store them in some folder on server or on your computer, and store link to photo in database. And having a separate table for links to photos allows to have several of them for item - e.g. before and after restoration.)
    tblArtists: ArtistID, ForeName, LastName, ...;
    tblItemTypes: ItemTyp, ItemTypComment;
    tblVendors: VendorID, VendorName, ...;
    tblTransactions: TransactID, TransactDate, TransactTyp, ItemID;
    (E.g. TransactTyp = 1 for item purchase, TransactTyp = 2 for sending item to restoration, TarnsactTyp = 3 for returning restored item, TransactTyp for selling item, etc.In case you need to keep item locations when in stock, you may consider adding tblLocations, and having LocationID in tblTransactions too. NB! This table is spine of your database!)
    tblTransactTypes: TransactTyp, TransactTypComment;
    tblPurchases: PurchaseID, ItemID, PurchaseDate, PurchasePrice, ...;
    tblSales: SaleID, ItemId, SaleDate, SalePrice, ...;
    tblRestorations; RestorID, ItemID, FromDate, ToDate, VendorID, RestorPrice, ...;
    (You can consolidate last 3 tables to one (e.g. tblActions with ActionTyp to differ between them), or you keep them in separate tables. And you need form-level code to keep actions in sync with tblItems and tblTransactions. When you purchase or sell an item, this must trigger an entry in tblTransactions, and reset item status in tblItems.)

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

Similar Threads

  1. Replies: 6
    Last Post: 07-10-2019, 07:34 PM
  2. Replies: 4
    Last Post: 03-19-2019, 08:35 AM
  3. Replies: 1
    Last Post: 04-10-2017, 10:06 AM
  4. Replies: 1
    Last Post: 06-03-2016, 03:58 AM
  5. Replies: 1
    Last Post: 05-28-2014, 10:59 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 - Senior Forums