In need of advice

[BenW]

Hi. I just recently started studying Access independently since my school never taught it to me and I'm trying to design a invoice type of database as a summer project. I'm stumped on queries because the office 2000 guide I have only briefly goes over it.

Basically, what i'm trying to do is create an automated value like in excel so that the "Net" column i have will subtract with the "sales" column to automatically enter a value for the "profit" column. I can't find any place for me to enter anything like [profit] = [net] - [sale]. i tried to use the input mask but since my data is in currency, it won't allow me to do it. can anyone please tell me where to start or what i've been doing wrong? thanks.

btw, i'm also trying to do the same thing with the y/n feature of access. i'm also trying to find a way so that if i type y/n for a column, it will copy the value from a different colum so say i put yes on "account R" then i want the "AR$" column to copy the value from the "sale" column automatically. if i can solve this problem the same way as the previous problem then please ignore this (i THINK this can all be solved with queries.)

 

[pbaldy]

In a query in design view, try this in a new column:

profit: [net] - [sale]

 

[BenW]

In a query in design view, try this in a new column:

profit: [net] - [sale]

thank you!! it worked out great (i was struggling and playing around with all of the commands for 2 days already). so if i wanted to do the same with the AR then would it be something like AR$: [sale]? how do i set it up so it only triggers when AR is answered as yes?

btw, i tried to create the same setup for the table in design view using default value and validation rule but it said that "net" wasn't a recognized default value. i tried to enter the same [net] - [sale] that you told me about beforehand.

thanks in advance


edit:
i ran into a problem when trying to make a total value

i created a new query with the value
net:sum([net]) and sale:sum([sale]) and profit:[net]-[sale] but when i tried to run it but it said i entered an invalid entry.

edit:
wait. ignore my last edit. i figured it out myself i made an error with the wording of the command.

 

[BenW]

a problem that i've noticed with my database is that everytime i save/make changes the size keeps getting larger. it was previously around 400 kb but now it is almost 1.5 mb. the only way i know how to reduce the size back to normal is to make a new database and import that data in, but is there a more efficient way to manage the file size?

 

[Banana]

Compact and repair will fix this; when you create a new record, edit record, or whatever, Access makes provisions to ensure that there is enough space to accumulate new data.

Be sure to search around for automatic compact and repair if that's what you may be interested in.

 

[pbaldy]

Banana has already addressed the size issue. On the table issue, you don't want to save that in the table anyway. The basic rule in database design is to never store a value that can be calculated from other values. Just calculate it in queries.

On the "account R" thing, in a query:

NewFieldName: IIf(AccountR = True, sale, 0)

Don't use spaces in your field names, or symbols like $. Both are a bad idea.

 

[BenW]

thanks for the help! i finally think i finished the project. now i'm just going to try to tune it up a little so it runs more effectively, but without your help i probably would never have gotten my queries finished (stupid ms office guide didn't help at all -.-"). once again thanks.

btw, how do you know all of those commands for access? did you learn them in school, a book, or from a webpage? if it is on a website can i have the link please?

 

[Banana]

For me, I'm mainly self-taught, by scouring MS Help files, MSDN library, this forum, Google groups and lot and lot of googling.

However, nothing would beat the organization of having a class or a reference book which I just got and started reading; learned lot of important little stuff that I didn't learn when I taught myself.

 

[pbaldy]

Like Banana, I'm self taught, with a lot of help from sites like this.

 

[BenW]

i've been trying to tweek the autonumber system to work to my advantage because i want to create a system so it keeps moving down numbers but doesn't have to start @ one.

currently my autonumber keeps going 1,2,3,ect....

whereas i've been trying to make it start at something like 6000,6001,6002, ect.... to make it a more practical invoice system, so is there anyway to change the default value of the starting number?

 

[Banana]

Then autonumbers isn't what you want.

Autonumber is there to create an unique value; it does not guarantee a sequential value.

If you wanted a meaningful invoice ID, I'd just add another field and create code to govern how it'd work. Something like this:

InvoiceID = DMax("InvoiceID") + 1

(This is air code and has wrong syntax; research the correct syntax)