Table Update issue (1 Viewer)

[cstacy]

Hello, I have an issue with a new database I'm trying to create (attached). I have two tables. One called Quote Number Summary, and One called Quote Data. The only purpose of the Quote Number Summary is to roll up quotes with the same number. I have a few quotes that share the same quote number and I wanted to created a form that only shows 1 header with all the quotes that share that number below. So if there are two number 1 quotes I only have one header on the form, but 2 entries in the subform. At first glance it seems to work until I try to enter a new quote number using the Quotation form which isn't on the Quote Number Summary table. I get the Error " You can't add or change a record because a related record in Quote number summary is required".

In reality I only want the one table for the data, but a form that will show the summary. I've tried a few different methods, but without this extra table I can't seem to get it to work.

I've attached the database with some sample info for you to look at. If possible I just want the people using the database to use the Quotation form to input data and for it to be included in the summary form with header and quote history. Unfortunately we have to have it summarised by quote number and not customer which doesnt help me.

Thanks again for any help you can give.

 

[John Big Booty]

Welcome to the forum.

You might benefit from normalising your table structure, this tutorial may help.

Additionally you might also want to consider a naming protocol for your DB objects and controls, something along the lines of; TBL_TableName, FRM_FormName, QRY_QueryName etc. Also avoid using spaces and other special characters in names, limit yourself to alpha and numeric characters and the underscore (_)

 

[cstacy]

Thank you very much John, have been able to get the tables linked and working using that link. I haven't removed any spaces as of yet, and might see if it causes any issues down the road.

Instead of creating a new thread can anyone help with my next issue?

I'm using the statement =NZ([TOTAL MATERIAL CONTENT])+NZ([MACHINE CONTENT (£)])+NZ([BOC COST PER])+NZ([SEC OP COST PER])+NZ([TRANSPORT COST PER])+NZ([LABOUR CONTENT PER]) which adds up 4 sums on the form. I get an #Error until data is entered. I've looked everywhere and can't see how to add the IIF(IsError to this statement to supress the Error or return 0.

Thanks again, well on my way now.

 

[John Big Booty]

Your Nz() is not quiet right, as it stands your Nz() function is returning a Zero length string, when you have a null value, so you need to change your statement to;

=NZ([TOTAL MATERIAL CONTENT][B][COLOR="Red"],0[/COLOR][/B])+NZ([MACHINE CONTENT (£)][B][COLOR="Red"],0[/COLOR][/B])+NZ([BOC COST PER][B][COLOR="Red"],0[/COLOR][/B])+NZ([SEC OP COST PER][B][COLOR="Red"],0[/COLOR][/B])+NZ([TRANSPORT COST PER][B][COLOR="Red"],0[/COLOR][/B])+NZ([LABOUR CONTENT PER][B][COLOR="Red"],0[/COLOR][/B])

 

[cstacy]

Thanks Again John, unfortunately that formula change doesn't help, but I think the issue may be related to a Div/0 error. Once I figure out how to return a 0 for that formula I think the others may be OK. The formula is:
=(3600/[QUOTED CYCLE])*[CAVITIES]
So if no data is entered it returns the Div/0 error.

 

[vbaInet]

=(3600/[COLOR=Red][B]IIF(Nz([/B][/COLOR][QUOTED CYCLE][COLOR=Red][B], 0)[/B][B] = 0, 1, [/B][B][QUOTED CYCLE][/B][/COLOR][COLOR=Red][B])[/B][/COLOR])*[CAVITIES]

 

[cstacy]

=(3600/[COLOR=red][B]IIF(Nz([/B][/COLOR][QUOTED CYCLE][COLOR=red][B], 0)[/B][B] = 0, 1, [/B][B][QUOTED CYCLE][/B][/COLOR][COLOR=red][B])[/B][/COLOR])*[CAVITIES]

Thank you that worked perfectly. I also found another way to do it as well

IIf([QUOTED CYCLE]=0,0,(3600/[QUOTED CYCLE])*[CAVITIES])

This formula type seems to work for any formula that would return an error. You just find the field that would cause it, check if it's 0. If it is return 0, if not I am able to get the right result.

I've also decided to put the formula into the query and not the form. This way I can easily export to excel if needed, yet still not store the results.

Thank you for all the help though. I've learned so much over the last couple of days.

Next is reports and then switchboard. I just hope the Boss doesn't upgrade to office 10. Not sure what will happen to my database then.

 

[vbaInet]

Thank you that worked perfectly. I also found another way to do it as well

IIf([QUOTED CYCLE]=0,0,(3600/[QUOTED CYCLE])*[CAVITIES])

This formula type seems to work for any formula that would return an error. You just find the field that would cause it, check if it's 0. If it is return 0, if not I am able to get the right result.

Different ways to skin a cat.

I've also decided to put the formula into the query and not the form. This way I can easily export to excel if needed, yet still not store the results.

Sensible!

 

[cstacy]

I knew it was too good to be true. I've attached another version of the database with a load of changes I made to it. I tried to normalise it and up till now thought I did. If I go into my Quotation form and create 2 quotes one with Quote number 1 Revision A, and another with Quote number 1 Revision B I can't save the version B.

In theory it appears to work. If I create Quote number 1 then Quote number 2 it works fine, but I can't create more than one quote number 1.

Can anyone look at the attached database and help me out. This seems to be the only block for me at the moment. I really don't don't know how to make both the Quote Header and Quote Data tables work together.

Sorry I did want to mention one last thing. The only reason I have two tables is so I can group all similar quote numbers into one heading rather than have 3 headers with the number one I will only have 1 header with all revisions below.

Thank you again.

 

[Rabbie]

Several things jump out here

1. Table Quote Data does not seem to be normalised. It looks like there are several repeating groups of data.

2. It is very inefficient to have a primary key that is in Text format. You should consider having a numeric(autonumber) PK in Quote Header. This does not stop you having a Text Quote Number if that is a business requirement.

3. I think you need to look at the Join properties between your tables. The don't seem correct at present.

 

[cstacy]

Thanks for your Help Rabbie. Is there a better way to group these on a form then having two tables? I just need to be able to summarise quote numbers with a list that will allow me to jump to another form.

I'll try to add anothe autonumber as an index. I guess I would need one on my Quote data table as well?

Thanks again

 

[John Big Booty]

Perhaps this tutorial will help you get your DB normalised.

You might also want to consider a naming protocol for your DB objects and controls, something along the lines of; TBL_TableName, FRM_FormName, QRY_QueryName etc. Also avoid using spaces and other special characters in names, limit yourself to alpha and numeric characters and the underscore (_)

 

[vbaInet]

All your tables need restructuring of some sort.

1. Rabbie has already mentioned Quote Data needing to be normalized and this affects:

• First Quote Date / Last Quote Date - Should you not have a series of quotes from which you can pick the last date a quote was issued?
• At least four more tables can be made from Quote Data and linked to it via an AutoNumber ID - which would be the Primary Key per table
• Foreign keys of the Primary keys should exist in Quote Data
• I would imagine Quote Data can utilise a composite primary key instead of just one PK.

2. The two Rates tables will be linked to the Machine table and Labour table respectively.

This is just the basics because I don't understand your business logic so you will need to do some reading to get this db fully normalised:

http://support.microsoft.com/kb/283878
http://r937.com/relational.html

Edit: I was a bit late there

 

[cstacy]

This seems a bit above me at the moment. While I think I understand the purpose of normalisation, I can't seem to relate it to my database.

From my perspective all my data can remain in the same table. I just need a way to group the data by quote number so I can show all revisions against a quote number. Is this possible any other way?

Maybe if I try to eplain how I want this form to work? I want to use the quotation form as a primary way of entering new or potential jobs into the database. This is pretty straight forward and can be done from one table (maybe not very efficient though). Since jobs change we may have to requote. The quote number would stay the same since it's the same job, but I would increase the revision to show it's a new quote.

The only trick I need it to do is have a way to look at quotes by quote number and show the revisions against it. I created the Quote summary form which seems to do that if I split the tables in two. So instead of showing three records for quote 1 show only one record for quote 1 and the 3 or so revisions in a sub table against quote number 1.

The only issue I get is when I try to enter a quote number that has already been done such as 1 and change the revision. I get the error that the data can't be saved as it would change an indexed value or primary key.

I really feel lost with this. I think I know a way to do it, but upon execution I find the way I had it was better.

I do appreciate the help though.

 

[John Big Booty]

Data Normalisation is a corner stone of good DB design, and is the first thing you need to get your head around before you do anything else in your DB. Without this you are doomed to a world of great hurt

 

[vbaInet]

Data Normalisation is a corner stone of good DB design, and is the first thing you need to get your head around before you do anything else in your DB. Without this you are doomed to a world of great hurt

... and unrest

 

[cstacy]

Guess I'll be crying again on Friday when I try to do it. What's worse is I created a whole new database with two tables. It only had two feilds on each. One had Quote number and customer, the other had quote number and revision. I tried to link the Quote number on table 1 to the quote number on table two and I still couldn't get it right.

 

[John Big Booty]

Work through the tutorial I linked to in Post 2 and again in post 12. Whilst it does not directly address your design requirements it will give you some reference points and techniques that you will be able to apply in your current (and future) project(s)

 

[John Big Booty]

... sometimes in order to move forward you need to retrace your steps and start again from scratch.

 

[Rabbie]

Guess I'll be crying again on Friday when I try to do it. What's worse is I created a whole new database with two tables. It only had two feilds on each. One had Quote number and customer, the other had quote number and revision. I tried to link the Quote number on table 1 to the quote number on table two and I still couldn't get it right.

Try this approach

Table Quotes

QuoteNumber(PK)
Customer

Table Revisions

RevisionID(PK)
QuoteNumber(FK)
RevisionNumber (For this quote)

You should be able to link these tables using a join which shows all Quotes and any revisions that belong to a quote. I imagine you may have initial quotes with no revisions.


Base your form on a query that shows the linked data.

Hope this helps