June7 - See your PM and we will start here ...

[Stephanie53]

Ok lets start all over...

In the ROT_5K_ Assembly form:

Need when selecting customer that the ship to address only comes up for that customer that has the same bill to address

We have some customers that have a main office while the supplies go out to different oil rigs

Now I also want it to when we select the sales rep that it saves in a report to his sales

Now we are on to the ordering part…

• Customer orders the type of hose he wants (ROT_5K_Hoses)
• Then customer advises to which fittings and ends (ROT_5K_Subparts) he needs
• The sales rep should be able to piece out what the customer needs by the size they need and the end pieces they need in the Subparts.
• This is why I need to have when ordering hoses by the PrimaryID that when selecting the Subpart ONLY show the list with just the same PrimaryID that it matches to. But it doesn’t…ughhh

After the sales rep is done with the hose he has the option of Saving/Closing (to which it will be submitted directly to the Quote) or click on Submit to Quote so that he can order another hose if the customer needs another.

Now on this Quote (that is attached here along with the DB) is the format that is needed for this company.

And I have to tell you…that this is just ONE of many things I have to do for this data base..there is still a 10K hose, an Industerial Hose and Hydraulic Hose…but if I can get this one down…the rest will follow!!

Now do you see my delima?

[June7]

First thing need to resolve is the PK/FK link for tblCustDB and tblCustBillTo.

tblCustDB
ID (PK)
CUSTID (text)

tblCustBillTo
CUSTID (number)
CUSTOMERNAME (text)

In relationships builder:
tblCustDB.ID links to tblCustBillTo.CUSTOMERNAME - these fields are not the same datatype, should probably link to tblCustBillTo.CUSTID and remove CUSTOMERNAME from tblCustBillTo.

Regarding the ROT_5K_Assembly form - this form makes no sense to me. I can't even figure out purpose of this form. None of the subform container controls Master/Child Links properties are set. The subform frmCustShipTo can be constrained to show only records related to the customer but what purpose does that serve? What is tblMstrEmployee for? It has no relationship to tblCustBillTo, it does relate to tblMsterROT_5K_Hoses which is in another subform. Two subforms can be related by this technique http://www.fmsinc.com/microsoftacces...edsubforms.asp

You have tables in circular relationships which might be an issue http://www.codeproject.com/Articles/...atabase-Design

[Stephanie53]

Ok lets take this one step at a time...

I changed the relationship to link from tblCustDB.ID to CUSTID in tblCustBillTo instead of CUSTOMERNAME I changed from CUSTID (number) to (text)...correct?

Now as for the ROT_5K_Assembly form...

This is what I want it to look like and be used as such...but with little bit different functions..instead of Service it would be ROT_5K_Hoses and instead of Product it would be Subparts related to that hose chosen...



I am not sure I understand the Master/Child Links. The subform frmCustShipTo is due to the fact that we have customers that have a main office but the products are being shipped out to their oil rigs so we need to have record of where it was shipped to and for what was ordered for that oil rig for billing purposes too. MsterEmployees is so that these sales reps have record of thier sales to these customers as they get a commision on each of their sales. this is why all of this has to relate to each other one way or another for when reports are ran. I.E. Run a customer report shows all the orders sent, pending or still in quote status...when run report of employee see what orders have been completed, which are still pending and what quotes are still outstanding. I understand I may have a relationship and PK/FK screw up here and trying to fix it. Any help you can provide I really appreciate it.

Thanks!

[Stephanie53]

P.S. This copy of the quote i posted is from Norhwind Services template that I can not for some reason edit to make it the way I want it to go..I cant add our product, our employees or our customer data base. If you know how please advise me how I can cuz this will save me alot of time and frustration on this project

Here is where I got the template that I would love to have but cant get to edit...

http://office.microsoft.com/en-us/te...102489834.aspx

[June7]

CUSTID field in tblCustBillTo should be number field. The primary key of tblCustID is ID Autonumber field. Remember, pk/fk fields must be same type of data. Number to number, text to text.

I have downloaded several versions of Northwind (Traders, Services, Sales). Never had a problem editing them. The database in the link you provided appears to be the web version. Web databases are different. Design View is not available. Table edits are done in Datasheet View with menu commands. Edits of forms and reports are done in Layout View. I never tried to enter data to a web version, don't know if that can be done locally. Apparently, converting web version to local version is not easy. Google the topic.

Master/Child Links are properties of subform/subreport container control. Set them to the pk/fk fields of related tables and the records of main form and subform are synchronized.

You have multiple tables (Armer, Ferrule, etc) with identical field structure. Why not one table with another field for Category?

I don't understand tblCustShipTo. tblCustDB has fields for shipping address so just save the tblCustDB ID to a field in appropriate table, which would normally be an Orders table.

[Stephanie53]

Where are the template data bases that are NOT webbased that I can work with that is in the format as described to where I can edit fields (meaning add what I need to makle it work to their specifications that they want)

Doing this from complete scratch is so frustrating and any help I can get to manipulate and add what I need will be greatly appriciated.

Thanks!

[June7]

This link show an ERD image of Northwind database. However, I think the download is an SQL database.
http://northwinddatabase.codeplex.com/

Try this version http://office.microsoft.com/en-us/te...001228997.aspx
The download is a template (accdt) file. Use it to make an accdb.

Or try this http://databases.about.com/od/sample...ccess-2010.htm