Results 1 to 3 of 3

Designing Database to help family member

  1. #1
    dualvba's Avatar
    dualvba is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2012
    Kent, UK

    Red face Designing Database to help family member

    Hi there

    I am developing a database to assist a member of family, who runs an Aunction House. Their current database is really restrictive and so tied up on authorisation loops, they can't add any reports and I can't amend the database for them at all, and am having real difficulty exporting all the core data to be the foundations for my own new one.

    However, I have one table for the actual items, another for the customers (who can be buyers as well as sellers - I have set them up one sole account), I'd like to be able to add to my customer form, a sub form (perhaps) lisiting all items they have sold and another showing how many they have bought, as well as invoice history for both.

    My main question, at present, is that they currently use the items table, live on the day of the auction. They filter it for that days auction date, and add live into the table, who bought the item, the hammer price (what it sold for), as on the other side of the office, customers come up the counter to pay for their items, and an invoice is pulled off there and then.

    I had thought about exporting a query and them adding the data back in, but as they are using the table at the same time, it needs to be live.

    Can you suggest an alternative better way?

    I have added so far, the tables and populated them, as well as a number of queries, and a customer and item form. I've not added any VBA at all as yet. I wanted to get the nuts and bolts in place first.

    I'm not sure how to set permanent filter at the top of the table, so they can filter for that days auction only.

    I also want to include a search on the top of the customer form, so they can search by the persons name or ID number?

    I havent' added the invoice section yet, but they need to be able to pull up all items that customer has purchased and add them to an invoice, and for the invoice to stay in the history.

    Also need to be able to pull invoices for the seller. They can relist items for 3 weeks, then they need to be notified to come and collect their items, I've created a report, for all sold items on a particular day, as well as a list of unsold items, with the time attached, so they can relist for the following week, or contact the seller to collect.

    Can you think of a better alternative? I'd like to be able to add conditional formatting, so they know by colour if they need to go back, or can be relisted, like a RAG system (red, amber green), not sure if this is possible, I've not researched this as yet.

    Sorry, lots of questions. I've only joined today. I'm self taught from books and the web, and attended a few Access Courses last year and VBA and am now hooked.

    Thanks so much

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    Do they use the current database as a central tool in running their business?
    You may wish to start a data model from the Tables and relationships in the current database. That is for starting only, since you wish to make changes. You could post a jpg of these Tables and relationships.

    My advice is to get your tables and relationships designed based on the business needs.
    You can use the same values for buyers and sellers, but work with a Buyer table and a Seller table for your data model.
    Don't get too hung up on Forms before you get your tables sorted out.

    There is a conceptual data model for auctions at that may give you some guidance.
    There is a set of business facts associated with this model at which may help you with your model.

  3. #3
    dualvba's Avatar
    dualvba is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2012
    Kent, UK
    Thank you so much Orange

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

Similar Threads

  1. Need help designing database and programming
    By meissnerb in forum Database Design
    Replies: 1
    Last Post: 04-11-2012, 03:32 PM
  2. Help designing a Scheduling database
    By DuWayne in forum Database Design
    Replies: 2
    Last Post: 12-31-2011, 08:30 AM
  3. Designing a database for a log
    By neo651 in forum Database Design
    Replies: 3
    Last Post: 10-31-2011, 10:45 AM
  4. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 09:34 AM
  5. questions on designing database
    By schultzy in forum Access
    Replies: 1
    Last Post: 07-18-2009, 02:44 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
Tech Forums: Microsoft Office Forums