Recent content by DanChard

I thought you could using something like the following but this doesn't seem to work. Maybe someone else could suggest why... update table_name set new_column_name = (select old_column_name from table_name where record_id = 4) where record_id = 9; If you want to add a specific value you can...

I don't think it's possible in one query as you'd need two different types of query. The first would create the column (or you could do this manually but it would have to be done on every table), the second would populate the new column for every row.

How different can the format for a postcode be? Some examples of some of more weird postcodes might be handy. You might try something like the following to achieve what you gave in your example: update [WL NonRes BackUp] set postcode = left(postcode,instr(postcode,' ')-1) & ' ' &...

Or this: select name, details, temp1 as [call (y/n)], min(date) as [date (if y)] from (select customer.name, customer.details, iif(isnull(calls.date),'n','y') as temp1, calls.date from customer left join calls on customer.name = calls.name) group by name, details, temp1 order by 4 desc;

I have something similar to this which I use for keeping track of money: payments ======== payment_id (AutoNum - PK) payment_type (Text[3] - 'IN' or 'OUT') payment_amount (Currency) payment_detail (Text[50]) date_of_payment (Date) payment_confirmed (Yes/No) payment_entered_on (Date) I still...

You can use IIF to control what's displayed in place of particular values. In this case, the string " " (a space) is displayed in place of the value 0: SELECT date, transaction as [transaction/expense], format(null,'Currency') as [expense_amount], iif(format(amount,'Currency')=0,'...

Try this: SELECT date, transaction as [transaction/expense], format(null,'Currency') as [expense_amount], format(amount,'Currency') as [income_amount] from income union select date, expense, format(amount,'Currency'), format(null,'Currency') from expenses order by date;

Well if they're not too long and complicated then maybe you could post the SQL from the two queries and let everyone have a look at what's going on. That's usually the best way to get results!

I can't remember the exact circumstances but I've noticed the same thing (not exactly, but similar) in the past due to grouping. If you're using any group bys then you may want to consider them and see if they are making this happen.

I don't know if it would help but you might try using the distinct keyword to select only unique records in the 2nd query.

Are the fields being updated in the two tables exactly the same type and size? You should also check to see which field is actually defined as the primary key as this would probably make s difference.

Yes OK, perhaps being able to see the DB for myself might be useful. You can mail it to: monkeydchardattalk21.commonkey but kill those monkeys on either side first...

I try to avoid selected from queries within queries whenever I possibly can for exactly this reason. I'm sure it is possible to achieve whatever it is you're trying to achieve without changing the database layout but it's made very difficult to work out exactly what's going on without being able...

Hi there, I'm pretty much at a loss when it comes to makeable queries but if you post the SQL from the following 3 queries of yours: qryCSAuditTotalsSub, qryMerlinAudited, qryMerlinIncorrectTotals It should be possible to get everything into a single query and then go from there. This is...

You need to be viewing full details to see the comments. This can be enabled by selecting 'Details' from the 'View' menu.