IT Employment

Ask something
Discussion Locked

I Need Help with access query

0 helpful
3 replies

by amalijohn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table of student records they pay their fees in three instalments entered in the table as Payment1 Payment2 and payment3. To compute the balance paid by a student i created a calculated field that sums up all the payments and deduct from the Total course fee. I am not getting the required answer. Intead of 0 after full payment i keep getting 6 0r -6.

Also each time i create a table by entering data directly in datasheet view and i automatically assign a primary ke the numbering starts at 22 i wonder why

3 replies

by paymeister

I'm sure many here would be happy to help. We'll need the query, of course, to give anything specific (open in Design View, then go to View and choose "SQL View", copy, and paste into your own "reply" to your original question). But lacking that, I would encourage you to look for the way your system handles nulls. I've often been bitten by this, and find that I can write workaround query columns that suffice, like "TotalPmts: iif([Pmt1]=null,0,[Pmt1])+iif([Pmt2]=null,0,[Pmt2])+iif([Pmt3]=null,0,[Pmt3])". (I also recognize that this is probably doing it the sloppy or hard way, and someone with proper training could do an elegant job which is currently beyond me - hey, for self-taught, I'm pleased at my progress... perhaps someone could suggest a way that both amalijohn and I could profit from.)

The primary key assigns 22 because at one time or another in the testing, probably, you had 21 dummy lines that you deleted. The system remembers not to reassign those numbers and starts at 22.

You might also find the answer already waiting for you at www.experts-exchange.com. I participate in both groups - this one for a better understanding of office politics, and experts-exchange for specific technical questions.

by ayuknotna

Try this,

First, Open your table in design view, click on a numeric field, then double-check the field properties at the bottom. Make sure that there are no 6 or -6 value in all fields (do this step in all numeric fields).

Second, click on the primary key, make sure the NEW VALUES in General tab (at the bottom--Field Properties) says INCREMENT.

Goodluck.

This discussion is closed to new replies.