.Edit only edits first entry in table?!

[The Bey]

On what query is the report based?.

It's the query which is in the sample database. I've deleted the report because I couldn't get it to work. If you try it, set the report up based on the query in the sample, and hopefully you'll get the same problem (I use 'hopefully' in quite the opposite way to usual)

On my search form which runs a report based on a query, this is the SQL for said query:

SELECT tbl06AllLines.Plant, [Plant] & "-" & [TAG] & "-" & [No] AS UniqueID, tbl06AllLines.EquipmentNo, tbl06AllLines.Service, tbl06AllLines.ImpulseLineMaterial, tbl06AllLines.[P&ID No], tbl06AllLines.HydrocarbonsPresent, tbl06AllLines.Traced, tbl06AllLines.Lagged, tbl06AllLines.AdditionalAccess, tbl06AllLines.InstrumentType, tbl06AllLines.Comments, tbl06AllLines.RelatedEquipmentLocation, tbl06AllLines.FolderContainingPhotos, tbl06AllLines.DateOfInspection, tbl06AllLines.InsulationAction, tbl06AllLines.TracingAction, tbl06AllLines.MaterialAction, tbl06AllLines.TransitionAction, tbl06AllLines.DueDate, tbl06
AllLines.AdditionalAccessToBeInstalled
FROM tbl06AllLines
WHERE (((tbl06AllLines.ImpulseLineMaterial) Is Null) AND ((tbl06AllLines.HydrocarbonsPresent) Like "*" & [Forms]![F_SpecificCriteria]![txtHC] & "*") AND ((tbl06AllLines.Traced) Like "*" & [Forms]![F_SpecificCriteria]![txtTraced]) AND ((tbl06AllLines.Lagged) Like "*" & [Forms]![F_SpecificCriteria]![txtLag])) OR (((tbl06AllLines.HydrocarbonsPresent) Is Null) AND ((tbl06AllLines.Traced) Is Null) AND ((tbl06AllLines.Lagged) Is Null));

I tried combining it with OR but it still doesn't want to work. I'm sure I tried this same thing before and it worked almost to the way I wanted it.
I'm not sure how many null values are in the table in the sample so I don't know how ineffective this query will be, but hopefully you get the jist

 

[jzwp22]

Is there any way that you can create an Access 2003 file of your sample database and post that? Otherwise, I cannot do anything until tonight.

 

[jzwp22]

OK, I converted the DB to Access 2003. I was planning on working on it tomorrow, but in looking at your table more closely, it is not normalized properly which will make the query more difficult. You have a series of fields that represent actions & their dates. These actions should not be fields but rather records in a related table. It would not be of value to create a query and then normalize the structure. My recommendation is to normalize the structure first and then worry about the query. In order to help with the normalization, could you provide some more detail on what business process you are trying to model?

 

[The Bey]

I'm structuring a monitoring process for a number of items in my workplace.

As I said previously, I've had difficulty with getting the dates from a table because of my how I've gone about obtaining the results, where I've created 2 boxes and if a criteria is satisfied, it will select the appropriate result. I realise I've shot myself in the foot here by my lack of normalization.

Is there a way that I can use a query to check if any dates are between a certain date? I know there's the between function on the query's criteria, but is there a way that I can run it across 4 different dates and if any of the dates are between said timeframe then for it to return the results?

I think I could do this by creating multiple queries for each date and then another query combining the 4.. I think

Also, if I wanted to merge 2 table together which have the same column names, can I do that in a query and will it add them as new rows or columns? Because I'd like to be able to add them as new rows if possible

Much appreciation for the late night post..

 

[jzwp22]

Is there a way that I can use a query to check if any dates are between a certain date? I know there's the between function on the query's criteria, but is there a way that I can run it across 4 different dates and if any of the dates are between said timeframe then for it to return the results?

With a properly normalized table structure the above would be easily done with 1 query; with your current structure it is much more difficult and as you said will probably require multiple queries, but getting all the data back together from the separate queries will be difficult. I strongly recommend you normalize your structure to make your life easier now and in the long run.

Also, if I wanted to merge 2 table together which have the same column names, can I do that in a query and will it add them as new rows or columns? Because I'd like to be able to add them as new rows if possible

This is possible with an append query. Start with a standard SELECT query that pulls the information from 1 table. Change the query type to an append query. Access will prompt you for the name of the destination table. Then map the fields of the first table to those of the destination table and run the query. Each record (all fields you choose) from the first table will be appended to the second table.


In looking at your database further this morning, I noticed that you are using lookup fields (combo/list boxes) at the table level. Even though Access has this capability it is generally not recommended to use them. This site provides more details as to why they are not a good idea. Combo and list boxes are best left for use in forms. I noticed that some of the tables referenced in these lookup fields were not included in your gutted database you posted.

Just as some general recommendations, it is best not to use spaces or special characters (#,&,@ etc.) in your table or field names. Also, you want to avoid using Access Reserved Words as table or field names.

I would be willing to help you get your application normalized, but I would need to see your entire database (with no data initially). You can create a new database and import all the tables & use the structure only option.

 

[The Bey]

Sorry I only just noticed your response; much apologies for the delay.

I've attached an entire copy of the database but I've removed the raw data. Can you do anything with this?

I notice that the code's also been transferred so you can have a little rant about my shoddy programming skills

There're some more things that I need to do still, as it seems like I'm getting close to finishing this database... well, hopefully.

But let me know when you get to have a look at it because then I'll throw some more questions your way.

Again, thank you for all of your time

 

[jzwp22]

Not knowing much about what you are trying do with the database or what type of data you are working with makes it difficult to know how to help. Could you please provide a little background on your application?

 

[The Bey]

Yes it's a database to categorise and structure a monitoring system for impulse lines on site. I can send you a sample of each of the sections of data so that you can compile a working sample, but this will have to be tomorrow as I've left work now?!

 

[jzwp22]

it's a database to categorise and structure a monitoring system for impulse lines on site

My apologies but I have no idea what impulse lines are. Is it some kind of manufacturing operation?

An example would probably be beneficial as well as a more detail explanation of your process.

 

[The Bey]

Impulse lines are small bore pipework connected to larger piping, basically running a small sample away from the process, measuring a factor across it (velocity, pressure, level) then adding it back to the main stream. It's a primitive way of measuring characteristics of processes.

At the moment there is no running process for the monitoring of these lines, and as with everything else, over time these will fail. This is where I come in; I'm to develop a system where these lines can have running inspection program to ensure integrity of the lines.

Basically I've come up with a system where the user selects a bunch of answers from dropdown boxes about various characteristics of the pipe, and then an answer is output for "Actions" on the line's insulation, material, metallurgy transition and tracing (I figure I'll go into detail and then you can get a full picture)

Dependent on the each of the actions given for each seperate action, a date is output, and then this information is updated to the raw data and can be displayed by a number of different methods. This date relates to the time when the action must be completed by (4 dates; one for each action)

 

[jzwp22]

characteristics of the pipe, and then an answer is output for "Actions" on the line's insulation, material, metallurgy transition and tracing (I figure I'll go into detail and then you can get a full picture)

In terms of the characteristics you refer to, are these the separate tables in the DB you posted: corrosion, entryseal, materials etc. that all have essentially the same structure?

If so, then I think they should be in 1 table with each characteristic as a record

tblCharacteristics
-pkCharID primary key, autonumber
-txtCharName
-longValue

measuring a factor across it (velocity, pressure, level) then adding it back to the main stream. It's a primitive way of measuring characteristics of processes.

Where are you capturing the velocity, pressure etc. in your table structure?


How are you tracking from which batch the sample is pulled for evaluation?

the user selects a bunch of answers from dropdown boxes about various characteristics of the pipe, and then an answer is output for "Actions" on the line's insulation, material, metallurgy transition and tracing (I figure I'll go into detail and then you can get a full picture)

How/where are you storing the potential answers?

 

[The Bey]

Ah, you're looking too deeply into this.

I explained the concept of the impulse line to give you an idea of what they were, however the process itself isn't of interest; it's the integrity of the line.

I'm looking at it from a mechanical/material viewpoint, as opposed to a chemical/process viewpoint, so what I'm more interested in is the general output of the line quality.

I think you could be right with the generalization of each of the subcategories (corrosion, process fluid) but these values aren't stored, just used for calculations. If the record data were present, you'd see that each of the choices had a value beside it.

On the "frmAction" I've set the AfterUpdate on each combo so that each choice will have a numerical value assigned to it. Then these values are run through a sum and an action is output depending on where the total value falls - this has also given me a lot of grief, but I've kinda dealt with that.

The end action is then pushed onto a summary sheet, which is then used in an update query to edit the main table.

What I've been wanting to do is show the dates, for each line's actions, where the soonest date to today will be flagged up. The dates will be in 4 different columns, so could you help on how I could cycle through these and select the correct date? A slight problem I have here is that I want to view the date on a form, but the dates aren't stored in a table until the form's command is executed

 

[jzwp22]

I think you could be right with the generalization of each of the subcategories (corrosion, process fluid) but these values aren't stored, just used for calculations. If the record data were present, you'd see that each of the choices had a value beside it.

These values are what should be stored, the final calculated value should not be store and with saying that you would not need the update query you mentioned. Calculated values are generally not stored because doing so my compromise your data integrity.

What I've been wanting to do is show the dates, for each line's actions, where the soonest date to today will be flagged up. The dates will be in 4 different columns, so could you help on how I could cycle through these and select the correct date?

Having 4 date columns (fields) is not the right approach. Each action should be a separate record each record will have an associated date. You can then use queries to get the soonest date/action.

 

[The Bey]

I found this:

http://support.microsoft.com/kb/209857

Could I use something like this to sort dates? Sorry for the lack of response but I've been quite stubborn with my work and don't want to go back and change the layup of my things; still, I don't understand your last suggestion with the different records/query.

 

[jzwp22]

When you have multiple dates related to a record it implies a one-to-many relationship between the record and the things that occur on those dates. Normalization rules would record those "things" and their associated dates to be stored as records in a related table. What would happen if you need to store a fifth date or a sixth? You would have to restructure your table and all associated forms, queries and reports! Whereas, if they were records, you would just add more records; there would be no need to change the table structure.

That is just my 2 cents; ultimately it is your decision on how to structure your tables.