If you have the system backup(s) and the data in a partition of the system HDD, effectively you are only securing yourself from user error. If the drive goes bad or if you get ransomware/virus you still have no solution.
Unless the drive with the backup is offline or in the cloud / network don't consider that you have a backup.
I too have an SSD to boot and an HDD to store data in. For emergencies I've got a bootable USB with windows setup always available.
I should backup to one of my old drives, but I can't seem to find the time/energy.
For my work computer everything is in the cloud, so I don't really care.

Usually unless we act on something, nothing happens. I'm guessing that you have already binded the datagridviewer to your dataset, from the VB GUI.
Instead, just place an empty datagridviewer and connect it to your dataset whenever you wish programmatically.
Also, share what you've already done to see your approach because I'm guessing here.

I believe you are closing one parenthesis from the "FROM" clause in your where.
Try this :

        sqlQuery = sqlQuery & " INNER JOIN Barangay ON Borrowers.BRGYNO = Barangay.BRGYNO)) "
        sqlQuery = sqlQuery & " WHERE (LOANS.RELEASED >= @d1) and (LOANS.RELEASED <= @d2) "
        sqlQuery = sqlQuery & " ORDER BY LOANS.RELEASED ASC"

The FROM needs to close before going to the WHERE.

1st of all this is a very old thread. You should have opened a new one.
2nd I see you closing the Conn but never open it. Try to open it before or after you assign it to the command.
If this doesn't solve it, open a new thread, post the code again and let us know the specific error you are getting.

Good luck

Because you are not giving a lot of information, I cannot provide a specific solution.
Try to use a case when inserting or a trigger or use a stored procedure that handles this to insert .
Alternatively post your insert code, the db type and what is the program that will do the insert and we can probably help you to figure this out.

Generally the update will not update 3 tables at once and your syntax is wrong.
I assume that you are trying this because you don't know which of the 3 tables holds the record that you need to update.
Why don't you try to update all 3 tables in their own update?

   av.CommandText = "UPDATE TB1 SET  TYPE_TB1 = '"  & _
    stock_add.TextBox1.Text & "' ,COUN_TB1 = '" & _
            stock_add.TextBox2.Text & "' where ID_TB1 LIKE '" & _
            stock_add.Label6.Text & "' " & _
            "UPDATE TB2 SET  TYPE_TB2 = '"  & _
    stock_add.TextBox1.Text & "' ,COUN_TB2 = '" & _
            stock_add.TextBox2.Text & "' where ID_TB2 LIKE '" & _
            stock_add.Label6.Text & "' " & _
            "UPDATE TB3 SET  TYPE_TB3 = '"  & _
    stock_add.TextBox1.Text & "' ,COUN_TB3 = '" & _
            stock_add.TextBox2.Text & "' where ID_TB3 LIKE '" & _
            stock_add.Label6.Text & "'"
            Sav.ExecuteNonQuery()

If the update doesn't find a record with ID like the label6 then it won't affect anything. The question is what do you expect to happen when more than 1 table have the same ID as label6?

I believe that you problem doesn't lie with the if, but with how you give values to @Ucount and @Ecount:

select @Ucount = count(Username), @Ecount= Count(Email)
from userdata 
where Username=@nameUsername = @name and Email=@emailadd

If I give Username = aa and Email = 'asd@asd.com' and neither exists then it's OK, you insert the record. But if let's say the username exists in the table, but with a different e-mail - or the other way around - then you won't be able to see that as you are testing for both the name and the e-mail.
You need to either split the value assignment into 2 different selects:

select @Ucount = count(*) 
from userdata 
where Username = @nameUsername 

select @Ecount = count(*) 
from userdata 
where Email = @emailadd 

or drop entirely the second variable (It doesn't look that you are using it anyway) and go with:

select @Ucount = count(*)
from userdata 
where Username = @nameUsername or Email = @emailadd

This way you get the number of records with a match in either field. So in the above example if a user with username = 'aa' and a different user with e-mail = 'asd@asd.com' existed in your table, you would get a @Ucount = 2 instead of 0 that your query would return.

This is a bad design / idea / report and the solution won't be really nice.
It's been a while since I've done anything with Access, but since nobody else is replying I'm going to give you a general idea and hope you can figure it out.

As you said you are going to need the previous balance. You've easily set it as the last physical stock tacking being the 1st day prior to the period you are looking into, but if that's not the case you need to calculate it as you would the new balance.

It is a pretty easy query to calculate the quantities for Transfer, Return and Sale using a crosstab query. Use the TransactionType field for ColumnHeading, the ProductId for RowHeading and sum the Transaction (I'm not sure if that's the quantity) as the value. Use the dates as criteria, but don't show or group by them as it will mess up things.

Then you simply (!) have to join against the initial balance and calculate the new balance by adding and subtracting the relevant columns. Please take care to outer join as to make sure you include products with inventory but no movements in the set period and new products that didn't have inventory in the start of the period, but have transactions and possibly newbalance.

If you copy this to a second query that would insert into a table the last stock taking transaction for each item and use the starting date of ...

I'm not sure why you group by "Avg Completion Duration". That means that you want a separate record for each completion duration.
If you want the average to include results from several weeks then you also need to remove the week from the group by AND the select list. The reason here is that the average can't "belong" to a specific week unless it is calculated per week (which would mean adding it to the group by).

To sum up, you only group by with the columns that you don't want aggregation and you group by the columns that you want the aggregation to take place for each value of them. In example if you group by year then you want the average for each year, if you group by year, student then you want the average for each year and for each student,...

Good luck.

I'm not sure what you are trying to do, but try something like this:

Declare @msg as varchar(20)

SELECT @msg = '2014/9-1'

declare @slash as int 
declare @minus as int 

select  @slash = charindex('/', @msg), @minus = charindex('-',@msg)

DECLARE @yyyymm varchar(6) 

select @yyyymm = convert(varchar(4),left(@msg,@slash)) + convert(varchar(2),substring(@msg,@slash +1 ,@minus-@slash-1))

select @yyyymm

Please note:
1) I just typed this and didn't test it, some typos might be found.
2) It is advised that you check that charindex has returned values and that @minus is greater than @slash if you expect it that way.
3) It is advised that you check that year and month are actually integers before doing anything with them
4) I know that although I'm using dynamic positions to substring and left, I'm assigning the values to a fixed length var, a) but I don't remember if varchar(@slash-1) would work and I'm too bored to test it b) It would make things even more complex for you and finally c) I'm not gonna test it while @yyyymm is only 6 chars long.

Good luck

Assuming that you only need Mytime1 to Mytime10 (10 pivoted columns) only:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

If it's a dynamic pivot, one where Mytime can be anything from 1 column up to whatever, then you need dynamic SQL. Let me know and I'll provide.

In the Else part of the if..end if you are only setting the com, you are not executing it. Try executing the command you've just set.

try to do it with selection instead of content. Make sure you start with selection.collapse if you don't want the search to be within the selected area only.

Try this:

update Users set NW ='1' 
from Contacts left join Users
on Users.code = Contacts.code 
inner join Contacts.Country = Location.Country 
where Users.NW = Location.City

But be warned that it might contain errors or other typos, as it was written in the reply box and hasn't been tested.

Good luck

Edit: Just saw that this is in the MySQL subforum. The update supports from in MS SQL, but don't know what MySQL will do.

I disagree with IIM.
The select into is used when you want to select records into a new table (it is created by the into).
If you want to insert into an existing table, your syntax should be like:

Insert into table1
(field1,field2,field3)
select field1, field2,field3
from table2

I'm not sure about php, but I'm sure you'll find a couple of threads about resource availability in here (Databases category).
About inserting to more than 1 table, use a stored procedure.

I hope you've sorted the conversion - didn't login for a week or so, sorry about that.
If not you convert(int,custom_total_inbound) or convert(int, isnull(custom_total_inbound,0))

I'll be honest, I didn't read everything. But what I did read, makes me think that you are replacing null with 0 in your columns but not in your formulas.

Also it would be easier to read and create to use isnull() instead of case.

To make it easier for you, I would replace NULLs (either with case or isnull()) in the T derived table. This way you don't need to change your formulas and add additional complexity there.

Inside the triggers you get access to 2 "special" tables. Read here: http://msdn.microsoft.com/en-us/library/ms191300.aspx for info and how to use them.

@themaj: We are all looking for a solution that we can understand. To be honest, the 1st time I saw this query it was waaaaay out of my league.

@Reverend: Bandwidth is also a resource. In the system I've been supporting/developing until recently, bandwidth was way more valuable than server IO (Plus RF Guns don't have that many resources to use). Plus SQL is better at processing bulk data and it is faster than looping through it. We won't agree, but I enjoy this conversation and the different approach.

Sorry themaj for the continuous out of topic.

If it makes you feel better, put the derived tables in a view and use that. BUT:

  1. since it's producing it's own values, it will only fail when UNION or CROSS JOIN are no longer supported.
  2. It is a maintenance free query
  3. It has only 1 step. It is way easier to trouble shoot than to get the records into a dataset and then sort the dataset and then determine the missing dates and then present them to the user.
  4. If it's usage in this application is one per month, it's not going to stop working at 3 am.
  5. Efficient programming and respecting resources are what makes a good programmer.
  6. People reading what we post here aren't going to have the same requirements as OP. "Verba volant, scripta manent".

I totally respect your knowledge, age and experiences.
I've written my fair share of code in the last decade and almost always had problems with poorly commented code or code that didn't follow coding standards (naming a table or a global variable "a" or "aaa").
Long queries were never a problem - and trust me I've had sprocs quite big, undocumented, uncommented, written by a person in the States (I'm in Greece) and I was supporting it by myself. I've carried my laptop to the beach more times than my sun lotion, so I know the feeling of waking up and connecting to see why is everybody sitting instead of working, but I preffer this ...

thanks remember it is not in the database until its paid so will this work?

Yes. It calculates the dates based on TrailStartDate and a series of numbers from 0 to 99.

  • Dude! I've shot people for writing queries like that. It is not only unreadable, but it is also impossible to modif

You understand of course that the post box isn't exactly query analyzer - or what they call it nowadays.
This was about the logic more than anything else.
My query will pick out the missing dates, without having the user go through a billion records and have them do the job or getting a billion records to another layer and then loop through them.
This way of doing it is more efficient and faster. Looping throught records just isn't the way dbs work efficiently.

For the second query, is it I have to pass in the value via parameters?

No, but is makes it easier to keep track of things when you use parameters, so I use them to give you an understanding of what's going on.

I guess I forgot to mention that the above is limited to 99 months. It can be edited to support more, but I doubt anybody is willing to sell with more than 99 monthly payments - unless it's a house or something.

I'll give you the basic logic, which should work on all dbs, but the implementation will probably require some tweaking depending on what you write it for.

What you need is a list of integers from 0 to the number of days you are looking to check (if it's a year 365 days or whatever, the sky is the limit). After that you join to this list and dateadd your minimum date. if the date created in the dateadd can be joined to your table then you've got that day in your table. If you outer join then you can filter out the existing dates by where table1.date is null

MS SQL Syntax, but will work for MySQL with changes in the dateadd:

declare @min_DOB datetime
select @min_DOB = min(DOB) from table where StoreID = 1

SELECT x.date
FROM table
right join 
(select dateadd(m, num, @min_DOB) AS date from 
(SELECT a.id + b.id AS num FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS a
CROSS JOIN (SELECT 0 AS id UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) AS b 
CROSS JOIN (SELECT 0 AS id UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500
UNION SELECT 600 ...

It would help a lot to tell us what db you are using.
A couple things come to mind, which would work in MS SQL or MySQL, but not access and can't even tell if it would work on oracle.

Also, is this a one time thing or is it going to be run every x days (when you insert new month) ?

Edit: Does DOB contain only working days (Mon-Fri) or Saturday and Sunday are included? I'm guessing you are OK with checking the national holidays manually.

SELECT Field1, dateadd(m, num, TrailStartDate) AS date_due
FROM table
CROSS JOIN
(SELECT a.id + b.id AS num FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS a
CROSS JOIN (SELECT 0 AS id UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) AS b ) AS x
WHERE dateadd(m, num, TrailStartDate) <= getdate() --you might want to strip out the time
and --you need to figure out how to eliminate the paid months

Unfortunately I didn't have enough info to supply all the criteria (ie how you've named the fields and how do you determine if it's paid).

I haven't tested this and it's with MS SQL syntax, as that's easier for me. If you need help, provide the info and I'll rewrite it for MySQL

Why would you join and then include the same table in a (NOT) IN statement ? This is just a huge waste of resources.

    SELECT Facility.[Facility Name] FROM Facility
    LEFT OUTER JOIN Reservation ON (Facility.FacilityID = [Reservation].FacilityID
    AND [Check-in time] = '" & cboHour.Text & "' 
    AND [Check-in date] = '" & lstDate.SelectedItem.ToString & "') 
    WHERE ([Reservation].ReservationID Is NULL or [Reservation].CheckedIn = 2 )
    AND Facility.[Facility Type] = '" & lstType.SelectedItem.ToString & "'
    AND [Reservation].[Check-in time] BETWEEN DATEADD(hour, [Reservation].Duration, '" & cboHour.Text & "' ) AND '" & cboHour.Text & "')

If the rest of the query works, this should cover your cancellation needs, but again I think that you are not checking or the possible variations with time. I believe it should be - like the post I've linked to - something like :

   select f.[Facility Name] from Facility f 
    left join 
        (Select FacilityId, CheckedIn
        from Reservation 
        where [Check-In Date] = @CheckInDate
        and ([Check-in Time] <= @CheckInTime and [Check-out Time] >= @CheckInTime
        or [Check-In Time] <= @CheckOutTime and [Check-out Time] >= @CheckOutTime 
        or [Check-In Time] <= @CheckInTime and [Check-out Time] >= @CheckOutTime 
        or [Check-In Time] between @CheckInTime and @CheckOutTime
        or [Check-out Time] between @CheckInTime and @CheckOutTime)
    ) R 
    on f.FacilityId = r.FacilityID
    where (r.ReservationID IS Null or R.CheckedIn = 2 ) 
    and f.[Facility Type] = @FacilityType 

What this second query should do is check if there is a reservation in the specific date, where it starts before your desired checkin time and finishes during your desired period
or starts ...