Access tables - linking data

[dualvba]

Hi



I'm slowly getting to grips with Access, and use Access 2003 at work, so have installed same version at home.

This is probably a daft question, so I apologise in advance. i've searched the net, and gone through all 8 of my Access books, and I can't find the answer, but this could be because I don't know the right name for the function I need.

I have my main table, where sales are logged, each entry is logged, who bought what, per line, but someone could buy more than item on a day.

ie Main Table
Purchase Date BuyerName BuyerID ItemID ItemDescription Price InvoiceNo
31/12/2012 J Bloggs 4443 23 Desk 30.00

31/12/2012 J Bloggs 4443 26 4 chairs 30.00

31/12/2012 J Bloggs 4443 28 Bookcase 30.00

InvoiceDetails
Date
InvoiceAutoNo Date BuyerID BuyerName Totalitems TotalPaid

1001 31.12.2012 4443 JBloggs 3 90.00
1002 31.12.2012 93 CJones 4 45.00
I ran a report, which brought one line per buyer and by purchase date, and total number of items bought on that day, along with total value which I fed into a new table I have called tblInvoiceDetails, along with Invoice Number which is an auto number.

How can I pull the invoice number, from table InvoiceDetails, back into my main Sales table, by Purchase Date and BuyerID.

So if for example, that buyer bought 4 items on 31.3.2012 the same invoice number is applied to all 3 lines in my main table?

I now want to bring the invoice number back into my main report, by CustomerId and AuctionDate

I'm hoping its pretty simple, I tried to follow the NorthWind example, with main table and details table, but I'm not sure on there, if the queries populate the tables, or the tables were already populated and queries are based on the tables.

I was trying to think about how I'd do it in excel, and see if I could do it in Access, but i got myself a bit confused

I wanted an IF statement, that if ItemLog.CustomerID = BuyerInvoice.CustomerID AND ItemLog.AuctionDate = BuyerInvoice.AuctionDate are the same, then pull in Invoice Number.

But I couldnt get it to work

Once I've done this for my Buyers, I can do exactly the same thing for Sellers.

Thanks so so much for your help in advance
Clair

[RuralGuy]

It would help if the tables were normalized a bit further. Do you have control over the design?

[dualvba]

Hi there

Yes and I would rather do it at this stage, do you have any tips/ suggestions

What do u mean normalise?

Thanks so much
clair

[RuralGuy]

Good. The InvoiceTable should contain very little information, but all of the elements specific to the particular Invoice: PrimaryKey (Autonumber) (PK), InvoiceNumber, BuyerID, DateOfTransaction. The DetailTable contains *all* of the line items for the Invoice: PrimaryKey (AutoNumber), ForeignKey (PK of the InvoiceTable) (FK), ItemID, Price (for history in case price changes), Quantity. I think that should do it and allow you to show any of the fields you want in a query. THis scheme also requires a BuyersTable and an ItemTable that each have PK fields (hopefully an AutoNumber).

[RuralGuy]

The InvoiceTable could also have the Total of the Invoice if you want which might include Sales/Vat Tax. It would save calculating each time for some reports.

[dualvba]

Brilliant, thank you, would I be able to pull the invoice number in against the individual items via a query? I do have a separate customers(buyers) and suppliers(sellers) tables with their own primary key

I think I want to eleminate too many steps (queries populating more tables), as at present I am just bring the database up to date, and these invoices will be historical but good for audit purposes

But going forward I am going to add new dummy items for sale, so i can thoroughly trst it, and want to be able to issue invoices quickly, saving back info, I want to avoid duplicating records, I need to place/append/update the smaller table to issue a new invoice number for that customer, for that day, then apply that invoice number against all the things they bought and issue an invoice and then change a stays via a button as that item is paid (which could be used to stop duplicates and putting new transactions only for new invoice number)

But 2 people can be using the database at a time, one at the back desk entering purchases on the database against the buyerid, whilst someone is at front desk raising invoices and taking payments

I want to make it straight forward and clear

I really really appreciate your help, I want to do as much as I can in access, without getting lost and tied up n knots with vba
clair

[RuralGuy]

...would I be able to pull the invoice number in against the individual items via a query?
You would join the Invoice and Item table on the FK in the Item table in a query. *Every* item record would also have the Invoice number returned in the Recordset.
.
.
But 2 people can be using the database at a time, one at the back desk entering purchases on the database against the buyerid, whilst someone is at front desk raising invoices and taking payments.
When you deploy this db, be sure and split it and give each user their own copy of the FrontEnd.
http://allenbrowne.com/ser-01.html

[dualvba]

Thank you so so much RuralGuy :-)

[RuralGuy]

Are you ready to use the Thread Tools at the top of the thread and mark this thread as Solved?