Simple DB trouble - entities, tables etc

[MikeUK]

Hi

i currently have a simple spreadsheet in Excel containgin the following fields.

## Date - not unique
simpy shows the date of the transaction

## Debt - who the debt will be paid to - unique per date (e.g. CompanyA)

## Type of debt - what service is it - not unique by date or debtor (e.g. Wages)

## Net Price before VAT (X)

## VAT (value added tax, a UK tax) (Y)

## Price of Service (Z = X+Y)

## Running total of Net Price before VAT (X + X + X...)

## Running total of VAT (Y + Y + Y...)

##Balance - (Z + Z + Z...)

The spreadsheet currently calculates this automatically using simple formulas.

I'd like to create a nice little interface for this data entry using MS access forms. From then I can easily create queries and reports etc.

can somebody please help me out! I've tried to list my entities and create an ERD to no avail!

Many Thanks

Mike

 

[Uncle Gizmo]

I think your first that would be to see if you can import the static data into access, and then develop a query to calculate the dynamic data. once you have imported the data it should be a fairly easy step to create the forms.

For example do not import the Vat, import the Vat rate, 17.5% and sometimes a different value for different products. Then use this to produce your vat amount via a calculation in a query.

 

[MikeUK]

I think your first that would be to see if you can import the static data into access, and then develop a query to calculate the dynamic data. once you have imported the data it should be a fairly easy step to create the forms.

For example do not import the Vat, import the Vat rate, 17.5% and sometimes a different value for different products. Then use this to produce your vat amount via a calculation in a query.

hi
thanks for your reply

I was thinking of just using Access, and scraping the Excel all together. this is because when I did try the import, i needed to store the data in a table, adn therefore I will then have to copies of the data.

I was thinking of going down the route of using everything in Access, and using the simple formulae in the queries (if possible? it's been a long time since i used Access, can you use multiplication on number cells?) to add the data up etc.

I'll then create a very easy interface so that the person using it with no technical knowledge can use it with no problems. i.e. my Dad!

 

[MikeUK]

I'm trying to start with an ERD, and trying to eliminate the Many-to-Many relationships.

I can see 3 tables, a Debtor tabel, a Type of Debt table, and a Balance table containing the figures. I can't see what soft entities I need etc to make this work???

 

[Banana]

When you consider that VAT could change over time, but transaction's rate won't, you need to ensure that transactions made prior to change adhere to old value whereas new records now use the new value.

Also, considering that you shouldn't store a calculated value, I think you will find this article helpful in how to keep VAT current among with other data that might be dependent on time or soemthing like that.

 

[MikeUK]

thanks for your replies

I'm not really concerned with the VAT aspect of the data input, as it's usually written on the invoices that get input into the spreadsheet (and in future into the DB).

I've figured out my soft entity, a Payment Table with PaymentID, Date, DebtorID and DebtTypeID included.

any thoughts on this?

any idea on how I would create a running total of hte figures in the table??

thanks

 

[MikeUK]

in the Payment table of course there will be the cost, cost before Vat, etc...

 

[Banana]

Did you look at Northwind sample that came with Access software?

I'm fairly sure that you wouldn't store costs, but rather quantity and price, and let the database calculate the subtotal, VAT, then grand total. That way, the total is always correct should a mistake be found in the invoice and you don't have to fix anything other than that mistake. Storing the costs would mean you'd have to make sure that they were also updated, adding unnecessary maintenence to your database.