updating table?

[tom4038]

I easy one i guess but im still learning:

SELECT smslog.*
FROM smslog INNER JOIN qryMaxDates ON (smslog.SentTimeStamp = qryMaxDates.MaxDate) AND (smslog.PhoneNumber = qryMaxDates.PhoneNumber)
WHERE (((smslog.Data) Like "*io:BF*" Or (smslog.Data) Like "*FE*" Or (smslog.Data) Like "*7f*"));

The query above uses data from a table called smslog

The query above gives me all the data containing BF or FE or 7f, doesnt mean anything to anyone else but alot to me!!

I now want to update this information to a table, the table is called UnitStatus

It lists all the telephonbe numbers and one field will be called status, I want to update this field with the word 'FAILED' when the query is run.

Any ideas anyone, im abit of a starter but manged to get the above sorted out with a little help in a previous thread!

thanks

 

[jzwp22]

Create a query using both the query and the unitstatus table and join them via the phonenumber field. This assumes that your query only returns 1 record per phone number. Something along these lines:

UPDATE UnitStatus INNER JOIN qryFailed ON UnitStatus.phonenumber = qryFailed.phonenumber SET UnitStatus.Status = "Failed";

 

[tom4038]

Create a query using both the query and the unitstatus table and join them via the phonenumber field. This assumes that your query only returns 1 record per phone number. Something along these lines:

UPDATE UnitStatus INNER JOIN qryFailed ON UnitStatus.phonenumber = qryFailed.phonenumber SET UnitStatus.Status = "Failed";

Hi,

thanks for that, where would this code go?

before, after or in another query?

thanks

 

[jzwp22]

Create a new query similar to what I provided (you will have to substitute your own field, table/query names).

 

[tom4038]

i have tried this

UPDATE UnitStatus INNER JOIN qryLatestResultsALARM ON UnitStatus.phonenumber = qryLatestResultsALARM.phonenumber SET UnitStatus.Status = "Failed";

if i run the query from design mode to view i get this...

The query gives two results but , which is correct based on the data porovided but it does not update the field to "failed"...

if i run the query from the 'task bar' on the left hand side i get this...

error saying... Operation must use an updatable query

Anyone got any ideas?

thanks

 

[jzwp22]

I had a feeling that might happen. To overcome that error you will need a nested query. But the first thing to check is to make sure your qryLatestResultsALARM returns only 1 record per phone number. If not, you will have to alter that query possibly with the SELECT DISTINCT option
If that query only returns 1 record per phone number, then the next step is to modify that query to only SELECT the phone number field (i.e. do not use SELECT smslog.* but rather SELECT smslog.phonenumber) and embed that query in a new update query

The new (nested) update query will look something like this:

UPDATE UnitStatus SET UnitStatus.Status = "Failed"
WHERE phonenumber in (SELECT smslog.phonenumber
FROM smslog INNER JOIN qryMaxDates ON (smslog.SentTimeStamp = qryMaxDates.MaxDate) AND (smslog.PhoneNumber = qryMaxDates.PhoneNumber)
WHERE (((smslog.Data) Like "*io:BF*" Or (smslog.Data) Like "*FE*" Or (smslog.Data) Like "*7f*")))

 

[tom4038]

this is great.... problem solved, it was only returning one result per phone number. It's been good fun sorting this out and will SLOWLY! build the database over the next few weeks.

Next problem is automating this update query, and not being asked to give persimssion to update the tables... will do my research!

 

[jzwp22]

Glad that worked out. Here is one command that should work (and not give the message)

CurrentDb.Execute "yourupdatequeryname", failonerror

 

[tom4038]

I have another question!

I now want to design another update query... similar to the one above but updating any unitstatus to 'outstanding' when it exceeds 7 days since data contained "ff" if you know what i mean?

Is it just a case of putting in a criteria of 7 days??

 

[jzwp22]

I think to be able to help you, I will need to know a little bit more about the tables involved: smslog and others? I am also thinking that doing updates of the unitstatus table may be unnecessary; in fact the unitstatus table may not even be needed since you are basically storing something that can be determined just by using a SELECT query. Could you provide some more details about what your application is designed to do?

I'm thinking that you need a transaction history table for each phone number:

tblPhoneNumbers
-pkPhoneNumberID primary key autonumber
-txtPhoneNumber
other fields

tblPhoneNumberTransactions
-pkPhoneNumberTransID primary key, autonumber
-dteStatus (status date)
-status

 

[tom4038]

there is abit of info held within the unitDetails (previously unit status) ie. address contact information etc...

there were 5 status's....

alarm, where table smslog field data contains 'ff'
reset, where table smslog field data contains 'bf'
inspect, where table smslog field data containing 'ff' was recieved more than 'x' days ago
failure, where no smslog entry has been made for a phonenumber within 24 hours
offline, need the ability to update the status to offline

thinking about it the failure status might mean having to list all the phonenumbers within a table and comparing them with the ones within smslog prior to any quiries, kind of an authentification process.

your right thou i do need to have a historical log... then hopefully i can build the database around it. best to get this sorted now than later. Hope the above info provided some helpful info on the project!

thanks again

 

[jzwp22]

Based on the information you provided, it sounds like your smslog table can be modified to be your transaction table.

tblStatusDetails
-pkStatusDetailID primary key, autonumber
-phonenumber

tblsmslog
-pksmslogID primary key, autonumber
-fkStatusDetailID foreign key to tblStatusDetail
-dtelog
-fkStatusID foreign key to tblStatus

The tblStatus would hold the various status that are possible, of which you list 3 as indicated below. The status of "failure" would be calculated since it represents the state of the number after 24 hours since the last entry in tblsmslog (i.e. you do not need to actually assign/store this status, I am guessing.). Similarly, "inspect" also sounds like it is a calculated state and thus there would be no need to store it.



tblStatus
-pkStatusID primary key, autonumber
-txtCode
-txtDescription

The records in tblStatus would look like this:

pkStatusID|txtCode|txtDescription
1|ff|alarm
2|bf|reset
3|? |offline

 

[tom4038]

Based on the information you provided, it sounds like your smslog table can be modified to be your transaction table.

arr! smslog is a linked table to another access db, will modifying it cause a problem?

offline say zz

also what is meaning of pk?? i know it could be anything but why pk?

 

[jzwp22]

Yes, if smslog is a linked table and you modify it, the change will impact any other applications and their related forms, queries, reports and code that use the table. Are you responsible for the other DB? If not, then you will probably limited in what you can do.

pk denotes primary key. I generally use a prefix to help me identify a field type as shown below. You can use any naming convention you like.

pk=primary key
fk=foreign key
txt=text
dte=date
lng=long number
log=logical (yes/no) field