Results 1 to 14 of 14
  1. #1
    brujitonegro is offline Novice
    Windows 11 Access 2019
    Join Date
    Jan 2023
    Posts
    5

    suggestion/help with access table?

    Hello everyone, just looking for advice, it has been about 15 years since I use access.


    I'm trying to make a database for my ebay store. I have a total of 3 tables.
    Inventory -> Date ,Item name,category,purchase price, Location
    Item Sold-> item Name ,sold price, shipping charge,shipping fee, sold date,Fee,Profit
    Miles-> Date, address Miles.

    I want to know is it possible to be able to do a search on table(item sold) for the item instead
    of having typing it in again. I want to be able search for the item and market as sold and fill in the rest.
    Click image for larger version. 

Name:	itemsold.png 
Views:	61 
Size:	29.7 KB 
ID:	49521Click image for larger version. 

Name:	inventory.png 
Views:	56 
Size:	26.0 KB 
ID:	49522
    if any one can help me out with suggestion on how to get started would appreciated it.
    thanks in advance

  2. #2
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Attach your file.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    brujito-davegri-eBay.zip

    Here's a starter, but some design questions remain such as why is no quantity recorded? Do you ever acquire duplicate items in lots, then sell as singles (or smaller lots)?

    I see no reason to have 2 item tables, and what is Miles table purpose?

    Profit should not be a table column. It can be calculated as needed (like in the example form).
    The lookups for category, location and status have not-in-list events to allow you to add rows to the dropdowns.

    Click image for larger version. 

Name:	itemRel.png 
Views:	52 
Size:	35.2 KB 
ID:	49524
    Last edited by davegri; 01-20-2023 at 09:43 PM.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    Don't see why you need a status field - if datesold is completed, it's sold, if it's blank, it isn't.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by CJ_London View Post
    Don't see why you need a status field - if datesold is completed, it's sold, if it's blank, it isn't.
    Could be other Statuses, Returned, In Transit, Currently Bidding, whatever.

  6. #6
    brujitonegro is offline Novice
    Windows 11 Access 2019
    Join Date
    Jan 2023
    Posts
    5
    Thank you so much.. for the help and information. I drive allot to different estate sale through out the week. I have to keep track of the miles. 90% of the time I only have one item of a kind, once in a while do I have quantity in which the condition might be different, in which I would have to make a different listing.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    brujito-davegri-eBay-v02.zip

    Added notes.
    Added sort option. Should be useful when DB gets large.

    Click image for larger version. 

Name:	sort.png 
Views:	37 
Size:	26.3 KB 
ID:	49533

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I have to keep track of the miles.
    Do you keep track of miles for each item, miles daily, miles weekly, miles per estate visit, or ...

  9. #9
    brujitonegro is offline Novice
    Windows 11 Access 2019
    Join Date
    Jan 2023
    Posts
    5
    I hit a few sales once a week, some times no sales. Or I might drop packages to the post office. That's why I needed a Mile tracker and expense when I buy supplies. Where Can I put a formula. Sold price+ shipping - ebayfee-purchase price = profit. I have been doing manual. At some point I will add more functions to make this faster. trying learn first. Thanks again.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    brujito-davegri-eBay-v03.zip

    Added form Main as switchboard to control the new form loading.

    OK. Added mileage table, form and mileage log YTD report.
    Added table for vehicles.
    Added table, form and YTD report for supplies.
    Added form to launch reports.
    Added Item YTD sales report

  11. #11
    brujitonegro is offline Novice
    Windows 11 Access 2019
    Join Date
    Jan 2023
    Posts
    5
    OMG thanks so much.. !!! I do have a question. How come when I click for a new recorder, nothing happens. it doesn't save it or give me a new black record. The only way is to do a new record input all the info, go back to a old record, once you come back last record and click new record it give it to me. Once all info is provide I should be able get a new record. Let me know if that happens to you. Thanks in advance

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Do you know how to modify code? Here's the fix (add the red) for the procedure in frmItems

    Code:
    Code:
    Private Sub cmdAddNew_Click()
        On Error GoTo Error_Handler
        If Me.Dirty Then Me.Dirty = False
        If Me.NewRecord Then
        Else
            DoCmd.GoToRecord , "", acNewRec
        End If
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    Error_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddNew_Click" & "."
        End Select
        Resume Error_Handler_Exit
    End Sub

  13. #13
    brujitonegro is offline Novice
    Windows 11 Access 2019
    Join Date
    Jan 2023
    Posts
    5
    Great fix that code above, but the formula is not correct I try changing thinking it would give me the correct results and no luck.

    [CODEPrivate Sub Form_Current()
    On Error GoTo Error_Handler
    Dim nCosts As Currency
    If cboStatus.Column(1) = "Sold" Then
    nCosts = Nz(Me.PurchPrice) + Nz(Me.ShippingCharge) - Nz(Me.ShippingFee) - Nz(Me.Fee) - (nCosts)
    Me.txtProfit = (Me.SoldPrice)
    Else][/CODE]

    Click image for larger version. 

Name:	formula.png 
Views:	13 
Size:	51.5 KB 
ID:	49554
    When I press recalculate I get (3.97) It should be $18.69 (sold) +(shipping fee) -(fee)-(shipping fee)-(cost)=profit. Any suggestion would be appreciate. Thanks again.
    .... Need to buy you a cup of coffee for all your help.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If buyer pays shipping charge and you pay shipping fee and ebay fee then

    Code:
    Private Sub cmdRecalc_Click()
        Dim nCosts As Currency
        If cboStatus.Column(1) <> "Sold" Then
            MsgBox "Item not yet sold.", vbInformation + vbOKOnly
            Me.txtProfit = 0
            Exit Sub
        End If
        nCosts = Nz(Me.PurchPrice) + Nz(Me.ShippingFee) + Nz(Me.Fee)
        Me.txtProfit = (Me.SoldPrice) - (nCosts) + Nz(Me.ShippingCharge)
    End Sub
    Did you get my Private message?

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

Similar Threads

  1. Replies: 3
    Last Post: 07-02-2015, 09:15 AM
  2. Your Suggestion About Database
    By YYP in forum Access
    Replies: 5
    Last Post: 07-29-2014, 11:49 AM
  3. Suggestion on Designing an Access Application
    By ravikiran in forum Access
    Replies: 0
    Last Post: 07-16-2012, 03:55 AM
  4. I need suggestion in SQL
    By genesis in forum Access
    Replies: 0
    Last Post: 07-31-2009, 08:17 PM
  5. Need suggestion or help please
    By KLynch0803 in forum Programming
    Replies: 14
    Last Post: 06-11-2009, 10:21 AM

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