Search results

As requested. Query1 is the UPSERT query I was working on. As @sonic8 was saying, I was thinking to forget this method. It's not too elegant. But I would like to see a solution before going to VBA.

PIN is the new items from AVGITEMCOST query PRICEID is the existing ITEMS Table. So 5 new items have been detected DECKING 18G, DECKING 20G..... This is the query I'm using. Same error. UPDATE AVGITEMCOST LEFT JOIN tblITEMTEST ON AVGITEMCOST.PIN = tblITEMTEST.PRICEID SET tblITEMTEST.PRICEID...

I was trying to avoid the step to "Make a Table". The query AVGITEMCOST is a distinct query, initially I used it to make the Items table but now as new and unique Items are coming into the db, I wanted to used that query to update and/or add the new items to the main table. So what you guys are...

I was reading this thread https://www.access-programmers.co.uk/forums/threads/append-if-rows-do-not-exists-else-update-if-exists.320254/page-2 and was wondering how to do an UPSERT using a query (not a table) as the source. I am trying the method described here and I am getting a "Operation...

I'm trying to make a Opening/Closing stock query. So far I've managed to do it for 2022 but how would I go about using 2022 Closing Stock as 2023 Opening Stock? I've attached the db and the query "RMMOVEMENT" is my current progress. Side note: I've heard that keeping a table (rather than...

When doing data entry you can enter the month and day and Access will fill out the year, which is based on the PC's date. I have a backlog of data entry, how to set the year part of the date to the previous year, 2021 or even 2020 when doing data entry. I know you can change the PC's date and...

Got it to work. I put a primary key in the WEIGHTS Table, removed SALESUOM and fixed the joins in the CCQTYByMTH query.

Ok, I checked back the joins in AZPROMAXCOSTLV2 and I forgot to join AZPROMAXCOSTLV1 to CCQTYByMTH on CCID and MTHEND. That took care of the duplications but still unable to Aggregate the final query AZMAXCOST. Edit: Also removed SALESUOM from CCQTYByMTH

It's ungrouped. When you total it, it throws the error. Also the base query has only 5068 records but AZPROMAXCOSTLV2 has 66045 records. Are the joins wrong?

Hi guys, Can I get a second pair of eyes on the query AZMAXCOST. I'm trying to get the max cost by month for each variation of FGMID and RMSUBID. I'm using the same method in the query BRCMAXCOST and it works but AZMAXCOST is giving me an overflow error.

I've had this problem. Sometimes......checking the "Clear Cache on Close" box on the BE helps resolves this. Also remember to compact and repair the BE regularly.

I'm trying to create a bank reconciliation in Access. Here's what I have so far. Tables: BANKSTMT - Uploads the Bank Statement to this table. CHQREG - Keeps the listing of cheques issued, amount and cleared date DEPOSITS - Records of Deposits made and date. Queries (Grouped by Month End)...

Didn't realize that people were still answering since I marked it as "Solved". I decide to take @plog advice. So I made a query SELECT ORDERDETAIL.SKU, ORDERDETAIL.PONO, ORDERDETAIL.PODATE, ORDERDETAIL.BACKORDERAMT, (SELECT TOP 1 PODATE FROM ORDERDETAIL AS T1 WHERE T1.SKU = ORDERDETAIL.SKU...

Ok, noted Thanks @arnelgp

Good day everyone, can I get some help writing an update query for the REORDERDATE field to give me the following results +------+---------------------------+------+-----------+-----------+---------+----------+-------------+ | POID | SKU | PONO | PODATE | SUBTOTAL |...

So I think I made some headway, thanks to Allen Browne again http://allenbrowne.com/ser-58.html I restructured tblNIS SELECT RT.EffDate, Nz((SELECT MIN(RT1.EffDate) FROM tblNIS RT1 WHERE RT1.EffDate> RT.EffDate)-1,#12/31/9999#) AS EndDate, RT.WRange AS LowerWK, (SELECT MIN(RT1.WRange)...

I've attached the db. qryGROSSWKLY is the Cartesian query. in Query1, I want to replace the function in the field "NISE" with a Cartesian query. Hope this helps

Expected Results +-----+-----------+-----------+-------+ | EID | MTHEND | ERate | ENIS | +-----+-----------+-----------+-------+ | 39 | 8/31/2022 | $5,000.00 | 53.20 | | 37 | 7/31/2022 | $7,000.00 | 70.40 | | 43 | 8/31/2016 | $5,000.00 | 49.40 | | 40 | 7/31/2015 | $7,000.00 | 64.00...

Ok, having the ENDDATE set that would mean that the date 2-Aug-2022 would fall between 5-Sept-2016 and let's say 31-DEC-9999. So all the rates with the 5-Sept-2016 would be the current. The next parameter would be the ERate. If the ERate is 7000, lookup the max value in MRange that is not more...

Hey, thanks guys. I beginning to understand. Can this kind of query work on a table like this? If MTHEND = 2-Aug-2022 and ERate = 7000, using the MRange Field, would return ENIS = 70.40 or if MTHEND = 8-Aug-2016 and ERate = 7000, using the MRange Field, would return ENIS =64.00