Need help

[richardplr]

Hi

I have a program which show all purchase contract that have not delivered. I have a question now.

1) I don;t know why i cannot do entry at the form.

2) I have a field name delivery status and I want this field to show either CHECK, JIT OR LATE. (How to set the Delivery status field to appear any of this word), If the estimate delivery date is later then request date, then it will appear as CHECK and if the estimate delivery date fall within 2 days from request date, it will appear as JIT and if estimated delivery date is later then 3 days or more than request date it will appear as LATE.

I hope someone can help me on this.

Thank you


Regards,
Richard Tan

 

[richardplr]

Hi

I have a program which show all purchase contract that have not delivered. I have a question now.

1) I don;t know why i cannot do entry at the form.

2) I have a field name delivery status and I want this field to show either CHECK, JIT OR LATE. (How to set the Delivery status field to appear any of this word), If the estimate delivery date is later then request date, then it will appear as CHECK and if the estimate delivery date fall within 2 days from request date, it will appear as JIT and if estimated delivery date is later then 3 days or more than request date it will appear as LATE.

I hope someone can help me on this.

Thank you


Regards,
Richard Tan

Anyone can help me????

 

[monkeytunes]

After a quick glance, your ad hoc relationships in your query, built on a bunch of non-unique value/non-indexed fields, are what's stopping you. You're doing joins on fields that don't have unique indexes.

Glancing at your structure, and I'm certainly no expert by a long shot, you have a bnch of problems that are going to prevent you from achieving your goals. For instance - your tables are not normalized, you're storing concantenated names, you're repeating data like crazy, and you've set no relationships in the relationship window, even though your query (and form) will require Referential Integrity.

You need to take this bad boy back a couple of steps and address the structural problems at the table level - think about what data (i.e. salesperson's names, suppliers, etc.) can be moved to different tables (where it will be stored ONCE) and linked with a unique ID. Think about what data (i.e. the PCNo, the Product Code, etc.) will link your tables to one another, and how to structure it so that these fields can be unique/duplicated as needed.

It looks like you're trying to make your "Sub-Form" table a JOIN table between "Main Date" and "Product Inventory", but your Product Code field in "Product Inventory" is NOT unique, AND it has null values. This creates a many-to-many relationship, which Access will not allow. You need to at least fix the Product Inventory field to be unique and indexed, and then your database might (I say "might") be able to allow you to data-entry on your form. But I warn you now - if this thing is going to be in use for awhile, you're REALLY going to want to fix it...