WUGNET help - Access - Table Design

Hiding zero values in report

By: BruceM via AccessMonster. - When you see something with "AS" it means there is an alias for the field: Nz(---,0) , criteria >0 >SQL show below:- > >SELECT Products.ID AS [Product ID], Products.[Product Name], Products. >[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity >Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty >Purchased]-[Qt...

Store similar types of records all in one table or separat..

By: tryit - On Nov 6, 10:55am, John W. Vinson wrote: > On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit wrote: > >Suppose you want to have four different types of records. > > >Each of these records have numerous fields in common, and a few fields > >that are unique to each type of record. > > >Most of the fields are related to oth...

Organizing Table

By: Steve D - My intention was never to build a seperate table for each year, just a table that contained all of the years (tblYear), and one that contained all of the months (tblMonth - 12 records - no more, no less). The table I showed as an example would reference all of those individual tables so if I needed to change a version name or add a year I would do it in the respective tabl...

how to make name table and purchase table join and get dat..

By: captgnvr - D/Jeff & John Thank you for the response. Will stop using the lookup field in the table and will read up 'orders' in Northwind. When I am making the third table by using name field from name-table and one item field from item-table and price field, it changes the name-field name in table3. Anyhow will study more as suggested and wl get back. brgds/captgnvr -- capt...

Linking Two Tables

By: Gabe - Thanks John, you are correct, I joined the two tables and the process wasn't as bad as I thought it would be. ~Gabe "John W. Vinson" wrote: > On Wed, 28 Oct 2009 16:35:01 -0700, Gabe > wrote: > > >I have two tables in Access 2003, Master (Parent) and Hours (Child). > >Currently they share a One-To-One relationship. I need a record to...

Linked table question

By: Rocky - Not sure if anyone will see this request for clarification because the topic is quite old. If you do however see it, can you just confirm that what I'm hearing is that currently there is no way to have the Access DB linked SS be refreshed upon opening? I have a linked SS and I have to go into the link manager to have it refresh. I'm wondering if there's not a preference ...

I have a -1 and a 0 for values

By: Roger Carlson - There are several things you can do. First of all, you can create your field as an integer or byte field, storing 1 or 0. On your data entry forms, you could represent this with a combo box that displays Yes and No, but actually stores 1s and 0s. You could also display it with an option group with two radio buttons, again displaying Yes and No, but storing 1s an...

Not able to enter Chinese 4 byte characters(GB18030)in MSA..

By: Murali Choudari - We are using Microsoft Access 2003 and 2007 and we are seeing the problem while inserting 4 byte characters (Simplified Chinese char) values in subsequent records (ID column). Here ID column is having unique index and this is comming only for index columns only. sample values which i entered in database as follows. after intersting the secord record ID with an...

Training Database

By: PieterLinden via AccessMo - Glenda wrote: >I want a similar database, BUT I wamt to add variables. I want to track >'what training does the employee need to complete' based on the job title. >Would that be best accomplished with another table? Also, I'd like the form >to show only the courses required for each employee (based on their title). >Your assistance is appreciated. >...

Access 97 to Access 2007 issues

By: Gina Whipp - You're welcome from the both of us! -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Scott" wrote in message news:3D1A0872-31E3-46F7-B36D-CF02B67E0207@microsoft.com... > Thanks to you and Allen Browne. This was very helpful. > >...

Allow Duplicates

By: Graham Mandeno - Hi JUNEBUG You will need a query something like this: Delete from tblOrders as O WHERE O.Status<>"SHIPPED" and Exists (Select PO from tblOrders as X where X.PO=O.PO and X.Line=O.Line and X.Status="SHIPPED") In plain English: Delete from the table any record whose status is not "SHIPPED" if there is a "SHIPPED" record in the table for the same PO and Line num...

Autonumber as primary key

By: Allen Browne - To prevent problems with autonumbers, you need to understand what causes the problem so you can take action at the appropriate time. The article had all the info I'm aware of. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "J E Jensen" ...

DYNAMIC STUDENT DATABASE

By: Brad Hodges - Dale, Thank you very much for your "two cents" which in my small disorganized brain is fifty dollars!! I totally admire your style, yet wonder how many of those MVP's out there actually using naming conventions these days. "Dale Fye" wrote: > Brad, > > Just my 2 cents. You might want to consider using a naming convention so > that when you look at an obje...

Auto Number

By: Majic - On Sep 1, 2:56pm, "BruceM via AccessMonster.com" wrote: > Roger Carlson demonstrates another possibility, which is to use the form's > Error event to generate a new number. He demonstrates this in a sample > database (link all on one line):http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395&SID=... > > Alternatively, go to the main site:http://www.roge...

need assistance - novice access database

By: KARL DEWEY - I suggest using two tables, one for employees and another to record work. tblEmployee -- EmpID - Autonumber - primary key Last Name First Name Middle Name etc. Work -- WorkID - - Autonumber - primary key EmpID - Long integer - foreign key Agent code Individual Corportation Corporation Name contracting Specialist Contract Received - I assume this is start ...

Is there a way to tell where exactly a certain table being..

By: Jerry Whittle - Rich Fisher's excellent Find and Replace add-in might be just the ticket. It can change everything related to the table and change them also. If you register it (one of the best $37 I've ever spent), the cross-reference report can find what queries use which reports and forms. It's a good way to find orphans. http://www.rickworld.com/ -- Jerry Whittle, Microso...