Results 1 to 3 of 3
  1. #1
    dualvba's Avatar
    dualvba is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2012
    Location
    Kent, UK
    Posts
    18

    Question Creating invoices and keeping information stored back to a table

    Hi Thank you for all the help you have given me already.



    This seems abit long, to ask just a few questions, but I wanted to put into context for you what I'm trying to do.

    I'm loving using Access, and have searched the net, and trawled through my Access books, but am abit stuck/challenged setting up the invoices. I found some really good youtube video's, and the Northwind database is good, and I've tried to base my invoice set up on their order's. But I'm not sure how they populate the order details sheet and link the invoice number back.

    This is an auction database. To track items bought in for sale, and which are sold, relisted, or sent back to seller, they are charged for each relist. So need to be able to pull off buyer invoices, which are always done on the day, once the auction is over, or the area the buyer was interersted in, has finished they come up the counter to pay for their items. The auctioneer has a list at the lecturn, where he manually writes the hammer price, and buyer ID, these are ferried back to the office for entering, so database refreshes and is upto date on the spot, for whoever is at the front desk, to raise a buyer invoice.

    I'm now at the creating invoice stage. I've tested it up to this point, and am happy relisting, returning, adding items and selling.

    I'm pulling in all thier historic data, that I captured on my last visit (31.3.2012), so have all sales, and buys, etc. They weren't adding new rows in before, for relisted items, just overwriting the one line, whereas now I add a new row in, by LogID, so item number can be relisted 3 times, but has 3 different logIDs so they can now be invoiced for 3 x 1.20 (listing fee).

    I'm not able to capture their historical invoice data, and they know this, so I'm going to create new invoice numbers, per seller/buyer, for a specific auction date.

    I've set up a sellerinvoice table and buyerinvoice table, on this I wanted to capture basic invoice details, to allocate the invoice number, for that person, for that date (and total value and total items).

    I need to be able to then pull that invoice number back into the main items table. So I can then create an invoice, for all items that person has bought that day (and same for sellers).

    Buyers pay the hammer price (sold price) and 15.5% fee. Buyers pay listing fee's and 15.5% of the hammer price.

    The main items table, has all the transactions on it with buyer ID.

    The customers table, has all items and the seller ID.

    Do you think I should change this.

    I have populated my buyersinvoice and sellersinvoice tables, by 2 append queries. I currently have the invoice numbers on each, as the prime number and auto number, pulling in total number of items, total value, customer ID and full name. So I then get an invoice number per person, per date.

    On your help tip, you say to include a separate primary key (is this what you refer to as Foreign key - or is this an access function/term), but I do want it to create the invoice numbers automatically. Can I have 2 auto numbers?

    My main table, does have all the information on it, each transaction, with a unique primary key auto number (which I have called LogID), here you say to include the Foreign Key from the invoice table.

    I have made sure all my tables have a primary key, and where that PK is in another table, I have made that a Foreign Key.

    You say I can use the Foreign key to marry the tables together and pull the invoice number back into my item log table, would this be an update query?

    I really don’t know how to get the invoice number back to the itemlog table (individual sales).

    Should the buyersinvoice table be auto populating? How does it know to pull the invoicenumber back, for that buyer, for that date?

    Once I have managed to link my invoice tables, to my main itemlog(details) table. My next worry point, is how would I pull out new invoices to get numbers, and feed back. At the moment, my 2 append queries, pulled everything in the item log, into both tables.

    But going forward, both the front desk computer, and the back desk computer users, could both be trying to raise invoices for buyers as they approach teh front desk.

    And if I run them again, it will just pull everything back in, and duplicate the entire table. I'm really not sure if I'm explaining that right.

    At present, using 2 append queries, I pulled into my Buyer Invoice table (for example), from the item log, all customers, where customer id is not null, with auction date (group by), CustomerFull name (from Customers table by customerID) group by, AuctionID (table key figure) count of, Total Price (sum).

    I created a query to look for the items, then turned it into an append query, which populated my BuyerInvoice details table. Auto number is invoice number, so it has allocated an invoice number, for a buyer, in a specific date.

    Is this the right way to have done it? What do I do going forward, to capture and pull back new data?

    The item log table is where all the sale data goes, so if I, for instance, bought 10 items on 4.8.2012, as the sheets come in from the aunctioneer, my id and the hammer price is entered onto the database, so those 10 items are sitting there waiting for an invoice.

    What links do I need to put into place, that automatically an invoice number is generated, for that person, for that date for those 10 items?

    I want to be able to search for a buyer, and see what items are sitting there ready for invoice (I could do this by a subform) how could I select all those 10 items, click on raise invoice (a report I guess), automatically pull in the next invoice number, buyer ID, and auction date, and feed all that data back to the item table?

    Should I add a flag, invoice invoiced, that states if an item is invoiced, and create a query, for that date, tha feeds all that back, that don't have an invoice flag?

    Am I over complicating things?

    I hope that makes sense? Again, I really appreciate your patience and help. I've tried to break the database down into bits, and tackle one at a time, as its rather daunting, but I'm ok up to now, and I knew the invoice section would be the most complicated.

    After the last help points (thank you, again) I have looked up foreign keys, and implemented those, I've looked up junction tables, but not sure if thats the way to go?

    Thank you again, for your help and patience, I've cut and paste this message in, so I'm hoping it will post OK?
    Best wishes
    Clair

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Clair,
    Long posts can discourage some. You might try breaking down your issues into smaller pieces and someone may be able to help.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 4
    Last Post: 05-21-2012, 08:21 AM
  2. Replies: 1
    Last Post: 05-19-2012, 03:51 PM
  3. Design table - keeping worker status
    By snoopy2003 in forum Database Design
    Replies: 8
    Last Post: 02-23-2011, 12:48 PM
  4. creating Stored Procedure in Access 2007
    By shraddha in forum Access
    Replies: 5
    Last Post: 08-03-2010, 09:43 AM
  5. Calling Stored Proc in MS Access 2007 without creating query?
    By DistillingAccess in forum Programming
    Replies: 1
    Last Post: 08-03-2010, 09:38 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