New to access - projects database help needed!

[CharP90]

Hi all,

I am new to this site and also new to access itself.

I have started to create a basic project management database to use at work, rather than use this as a continuous database it will be used as a template for each new project (I do not have enough experience in access to design a complete database).

My first question is, I have designed a table and a form based on our client orders, I have managed to use a query to pull through the necessary info from our products list but the issue I have is we have three different set prices for our products; an early bird price, standard price and a late price. These are pre-set prices which are detailed in the products tables. When placing an order, depending on what dates have been set for the cut off of those prices I need to be able to allocate that price to the order (where as at the moment the form just pulls through all three prices). Any idea on how to do this?

My second question is, In addition to having the 3 pre-set prices sometimes, depending on client or project, we might assign a 'bespoke' or 'custom' price for a product if say for instance they have spent a lot of money with us. I will need an additional place to be able to freely enter this figure in rather than use a pre-set price. How would I do this?

Lastly, once all of the above has been sorted out, I need to turn these orders into invoices to pull through the correct info to sum the total and include VAT @20% but I will need to edit the VAT depending on whether they are VAT registered or if the VAT changes.

I know this is probably a lot to ask for, but any help would be very much appreciated.

I have attached what I have done so far for you to see.

Thank you

 

[ConnorGiles]

Hi Charlotte!

Welcome to AWF!

I will get right on this for you, But I shall not give you the answers straight out of the bag, - This would not help you in the slightest.

Try playing around for a while and I shall comment again soon!


EDIT: I cannot seem to open your database, what type of access do you use?

 

[ConnorGiles]

I would need a copy of your database to show you what to do

 

[jdraw]

Do you have a clear set of specifications? A paragraph in simple, plain English of the business your database is going to support would be a great starting place for readers and for you.

I advise against the following that I see in your database:
Do not use names with embedded spaces for fields or objects.
Do not use Lookups at the table field level.

Good luck with your project.

see this thread for related info
http://www.access-programmers.co.uk/forums/showthread.php?t=237359

 

[CharP90]

Hi Conor!

Thanks!! I am using Access 2013 I believe - from office 365! I have reattached if that helps at all, or please let me know if there is another way I can send this?

Thanks!!

 

[ConnorGiles]

Try saving it as access 2007 for me Charlotte

I'll see if that helps.

Also try the compact and repair setting before linking this database in case of errors or corruptions

 

[CharP90]

Do you have a clear set of specifications? A paragraph in simple, plain English of the business your database is going to support would be a great starting place for readers and for you.

I advise against the following that I see in your database:
Do not use names with embedded spaces for fields or objects.
Do not use Lookups at the table field level.

Good luck with your project.


Hi!

Basically we build exhibitions so once we are set to build a show we open up a new file. At the moment I have it all working in excel with quite complex formulas and macros working it all out for me and spitting out an invoice but we need to make this more user friendly and we cannot share the excel sheet because of the formatted tables and macros.

We will be given a list of the exhibitors to exhibit at the show and we will need to note what we are building for them in terms of their stand sizes and what category (hence the shell scheme, custom etc list).

From this they will also place orders with us from set order forms which I have populated into the products list and from this I will need to send them an invoice.

I would also need to run reports to see who has not paid (we would enter the payment details in manually) so we can chase these on site and I would also need to run some form of report of products to send on to our suppliers to place the orders with them, some of these are in-house departments and some or not.

I hope that makes some sort of sense. I can upload the excel sheet if that helps?

Could you please let me know how I would do the lookups otherwise?

Thanks for your help,

Charlotte

 

[CharP90]

Right, here it goes.... does compact & repair and saved as 2007!

 

[ConnorGiles]

Do you have a clear set of specifications? A paragraph in simple, plain English of the business your database is going to support would be a great starting place for readers and for you.

I advise against the following that I see in your database:
Do not use names with embedded spaces for fields or objects.
Do not use Lookups at the table field level.

Good luck with your project.


Hi!

Basically we build exhibitions so once we are set to build a show we open up a new file. At the moment I have it all working in excel with quite complex formulas and macros working it all out for me and spitting out an invoice but we need to make this more user friendly and we cannot share the excel sheet because of the formatted tables and macros.

We will be given a list of the exhibitors to exhibit at the show and we will need to note what we are building for them in terms of their stand sizes and what category (hence the shell scheme, custom etc list).

From this they will also place orders with us from set order forms which I have populated into the products list and from this I will need to send them an invoice.

I would also need to run reports to see who has not paid (we would enter the payment details in manually) so we can chase these on site and I would also need to run some form of report of products to send on to our suppliers to place the orders with them, some of these are in-house departments and some or not.

I hope that makes some sort of sense. I can upload the excel sheet if that helps?

Could you please let me know how I would do the lookups otherwise?

Thanks for your help,

Charlotte

No luck

I'm just going to try and help you without the database.

What I think is best for you would be a form in which takes its data from a query.

on the form the field which shows the price would have some programming needed - such as checking the current time

 Time()

Then this would be related to an if statement - such as

If Time()  Between >=00:01 and <=10:30

Early Bird

If Time() >= 10:31 and <=18:00

Mid Day

If Time() >= 16:01 and <=23:59

Late

and of course the discount could be easily achieved by checking the price is under a certain threshold - If its over £20 for example. giving a 10% discount is as easy as *0.9 the current price.

I have Put these peices of code there on purpose - They may or may not work, because I want you to try and solve this riddle yourself.

All it takes is a little googling

 

[AccessBlaster]

To add to this, I would enforce referential integrity in your relationships.

 

[jdraw]

charp90,

Your expertise in excel may hinder your activity with Access -- spreadsheet is quite different than database.

For a great intro to concepts with examples you might want to spend an hour or two working through this tutorial from RogersAccessLibrary.