ACCESS FEED: September 2019

Senin, 30 September 2019

[AccessDevelopers] File - Monthly_Notices.txt

Monthly notices:

Hi Kids!:

Don't forget to check out our "Links" section at the website for helpful sites. Also take a peek at books that others have found worthwhile in our books database under the 'Database' link of the main AccessDevelopers page. Feel free to add any books or links that you have found useful.

__._,_.___

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (183)

Please zip all files prior to uploading to Files section.

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Rabu, 25 September 2019

[belajar-access] Halo, milis-ku ..... :D

Jika tidak ada robot message untuk aturan milis, group ini sudah mati bertahun-tahun.

MS Access dan perkembangannya , bagaimana dan kemana, adakah lagi yang ingin membahasnya ?

Ada yang masih aktif menggunakan di perkantoran atau di usaha sehari2 ?

yuk, silakan kembali berbagi .....

Aksan Kurdin

__._,_.___

Posted by: aksan.kurdin@gmail.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

SPAM IS PROHIBITED

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Selasa, 24 September 2019

RE: [AccessDevelopers] New Applications

I still use it regularly. Much simpler than most of the alternatives.

From: AccessDevelopers@yahoogroups.com [mailto:AccessDevelopers@yahoogroups.com]
Sent: September 18, 2019 10:02 PM
To: AccessDevelopers@yahoogroups.com
Subject: [AccessDevelopers] New Applications

Anyone still using MS Access for new applications from scratch? I don't know why access gets such bad PR. Been using it for over 20 years and been rock solid reliable except during the form development some flacky thing happen once in a while.. How does File Make Pro compare to it? Want to create a whole new database for a new purpose and debating if I should do it in access or look at something new. Also been happy with my data in SQL on my local server but would be nice to put it up in the cloud cross platform and not a big deal to me. Access no learning curve anything else would be.

Virus-free. www.avg.com

__._,_.___

Posted by: "DRR" <administrator@grand-resorts.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Please zip all files prior to uploading to Files section.

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Rabu, 18 September 2019

[AccessDevelopers] New Applications

__._,_.___

Posted by: rbs@drradio.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Please zip all files prior to uploading to Files section.

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Senin, 16 September 2019

Re: [MS_AccessPros] Combine 2 queries into one question

Duane,

Thank You, that was it.

Jim Wagner

On Monday, September 16, 2019, 1:48:16 PM MST, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Jim,

I expect you could use a single query with a subquery in the criteria:

SELECT *

FROM [PSS DATA]

WHERE ID = (SELECT Max(ID) FROM [PSS DATA]);

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, September 16, 2019 2:26 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Combine 2 queries into one question

Hello all,

I have 2 queries that make a table that gets the max of an id and last balance in a table. I find it difficult to understand why I cannot do this in one query. The first query is a select query that gets the max of the id in the PSS DATA table. Below are the sql statements. Is there a way to combine these queries into one? I have tried the below under Query1 but it does not give me the last balance. I do get a balance but it is a balance 5 records from the last record. and the max Id is correct just not the last balance

Thank You

Jim Wagner

Query1

SELECT Last([PSS DATA].BALANCE) AS LastOfBALANCE
FROM [PSS DATA];

CURRENT PROCESS

qry_updateBegBalance1

SELECT Max([PSS DATA].ID) AS MaxOfID
FROM [PSS DATA];

The second query is a make table that uses the the qry_updateBegBalance1 query and the PSS DATA table to make a table named tempBalanceTotal.

qry_updateBegBalance2

SELECT [PSS DATA].BALANCE INTO tempBalanceTotal
FROM qry_updateBegBalance1 INNER JOIN [PSS DATA] ON qry_updateBegBalance1.MaxOfID = [PSS DATA].ID;

__._,_.___

Posted by: Jim Wagner <luvmymelody@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] Combine 2 queries into one question

Hi Jim,

I expect you could use a single query with a subquery in the criteria:

SELECT *

FROM [PSS DATA]

WHERE ID = (SELECT Max(ID) FROM [PSS DATA]);

Regards,

Duane

Hello all,

Thank You

Jim Wagner

Query1

SELECT Last([PSS DATA].BALANCE) AS LastOfBALANCE
FROM [PSS DATA];

CURRENT PROCESS

qry_updateBegBalance1

SELECT Max([PSS DATA].ID) AS MaxOfID
FROM [PSS DATA];

The second query is a make table that uses the the qry_updateBegBalance1 query and the PSS DATA table to make a table named tempBalanceTotal.

qry_updateBegBalance2

SELECT [PSS DATA].BALANCE INTO tempBalanceTotal
FROM qry_updateBegBalance1 INNER JOIN [PSS DATA] ON qry_updateBegBalance1.MaxOfID = [PSS DATA].ID;

__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

[MS_AccessPros] Combine 2 queries into one question

Hello all,

Thank You

Jim Wagner

Query1

SELECT Last([PSS DATA].BALANCE) AS LastOfBALANCE
FROM [PSS DATA];

CURRENT PROCESS

qry_updateBegBalance1

SELECT Max([PSS DATA].ID) AS MaxOfID
FROM [PSS DATA];

The second query is a make table that uses the the qry_updateBegBalance1 query and the PSS DATA table to make a table named tempBalanceTotal.

qry_updateBegBalance2

SELECT [PSS DATA].BALANCE INTO tempBalanceTotal
FROM qry_updateBegBalance1 INNER JOIN [PSS DATA] ON qry_updateBegBalance1.MaxOfID = [PSS DATA].ID;

__._,_.___

Posted by: luvmymelody@yahoo.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Selasa, 10 September 2019

Re: [MS_AccessPros] Inventory Aging

Duane,

I think I had a breakthrough on this today. I changed the NZ function from 0 to use the on hand qty. Preliminary tests look good. I'm testing it on all the data tonight. It takes about an hour to run through all the records. I will let you know tomorrow if it worked. Thanks for your good advice.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,

Sorry about messing up your name. I need more coffee.

I would try move the Nz() to address only the DSum()

Nz(DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = 'R' and MAXID > '" & [MAXID] & "'"),0)-[onhand_01] AS LeftSum

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, September 10, 2019 9:13 AM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Inventory Aging

Hi Duane,

I'm not Art, I'm Doyce but your description is correct. Here is some sample data:

**qryInvAgingSelect**
MAXID	PartID	TDate	COST_01	TNXQTY_15	RunTotal	onhand_01	D	LeftSum	AgeQty
2792852	1001004	9/28/2016	12.6	1	1	3	-1	0	1
2248382	1001004	7/23/2014	12.6	3	5	3	-1	-1	1
2248716	1001004	7/23/2014	12.6	1	2	3	-1	-2	1
2248386	1001011	7/23/2014	2.7	8	9	7	-1	-6	6
2248723	1001011	7/23/2014	2.7	1	1	7	-1	0	1
2248387	1001012	7/23/2014	2.7	8	9	7	-1	-6	6
2248724	1001012	7/23/2014	2.7	1	1	7	-1	0	1
2785852	1001017	9/13/2016	10.8	2	2	1	-1	0	0
2785854	1001020	9/13/2016	24.05	2	2	1	-1	0	0
2785909	1001022	9/13/2016	2.25	4	4	2	-1	0	0
2785910	1001024	9/13/2016	1.5	2	2	1	-1	0	0

I have already created a report based on this query that puts the amounts in the correct aging buckets. If you notice in the data above, the parts that have more than one receipt, the AgeQty and LeftSum calculations are correct. However, if a part has only been received one time, the LeftSum and AgeQty calculations return zero. This is because of the NZ function in the formula. If I remove that from the formula, the calculation returns a null. I'm thinking there is a better way to calculate the LeftSum. If I can get that fixed, I think the AgeQty will calculate correctly. Here is the SQL for the query:

> '" & [MaxID] & "'")-[onhand_01]),0)),0)<=0,True,False) AS D, Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MAXID] & "'")-[onhand_01]),0) AS LeftSum, IIf([RunTotal]<[Onhand_01],[TNXQTY_15],Abs((Nz((DSum("tnxqty_15","Transaction

History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MaxID] & "'")-[onhand_01]),0)))) AS AgeQty
FROM [Part Master] INNER JOIN [Transaction History] ON [Part Master].PRTNUM_01 = [Transaction History].PRTNUM_15
WHERE ((([Transaction History].TNXCDE_15)="R") AND (([Part Master].onhand_01)>0))
ORDER BY [Transaction History].PRTNUM_15, [Transaction History].TNXDTE_15 DESC;

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Hi Art,

I believe you want to start with the number on hand (current inventory) and step backwards chronologically to see when you can account for purchasing a specific quantity and the price paid. The purchase date will identify the bucket the quantity and price go into. When all of the number on hand have been accounted for you are done putting prices and quantities into buckets.

Do you have some sample records and how you would want these reported?

Thanks,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, September 9, 2019 4:36 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Inventory Aging

Hi Rob,

No, that's not quite it. I need to know how long my inventory has been here. If I have a part with more than one receipt, I need it divided by receipt date. Example part A has 50 on hand. I received 20 in May and 10 in July and 20 in August. I need to know that if Part A cost $1 that I have $20 of current inventory, $10 of 60 day inventory and $20 of 90 day inventory.

Thanks for taking time to answer.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

Hi Doyce,

If I understand right, your issue is whether what you still have on stock was purchased for the last time.

So you need a query that gives the last receiving date of items with OnHand_01 <> 0. That's a simple join on the two tables.

Next you use that query in a query that groups on month of the last purchased date. Unless I misunderstand what info is needed, that would do the job I think.

Rob

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: donderdag 5 september 2019 06:08
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Inventory Aging

A couple suggestions:

Every time I read about a specification for "buckets" I create a table of "buckets" with MinBucketValue, MaxBucketValue, and BucketTitle.

Is MAXID numeric? You are using a numeric operator ">=" and yet comparing it with a string.

You are calculating LeftSum in the query and also using LeftSum in other expression. I never do this. I would repeat the LeftSum expression/calculation in place of using LeftSum elsewhere.

You do the same thing with SeqID. Why not use [Transaction History].MAXID?

I would always make sure there is a space prior to the "and". You have ..."'and TNXCDE_15...

This might or might not resolve your issue but I would sleep better.

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 4, 2019 2:54 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Inventory Aging

Hello Pros,

I have been ask to come up with a way to "age" our inventory into buckets based on receipt date using FIFO. "Current" inventory has been received in the last 29 days. Inventory received between 30 and 59 days ago would go into the "30 day" bucket and etc. I have approached doing this by going through the receipt transactions in our Transaction History table in descending date order and comparing the transaction Qty to the onhand qty. The following SQL is almost getting me what I want. It works fine as long as there are more than 1 receipt for a part. If it is a part that has been purchased and received only once, the AgeQty calculation fails and returns 0. Here are the fields in the query and a brief description of each:

SeqID is an alias for MaxID which is the primary key in the transaction history table. It is autonumber.

TDate - Transaction Date (Receipt Date)

TNXCDE_15 - Transaction Code. "R" is an inventory receipt

Cost_01 - The cost of the part. Used to value the inventory.

TNXQTY_15 - The quantity of the part being received.

RunTotal - The sum of the parts received starting with the most recent receipt in descending date order

Onhand_01 - The total number of parts we currently have onhand

D - a Boolean as to whether this receipt should be included in the aging.

LeftSum - How many are left to allocate

AgeQty - how many are in this age bucket.

Part Master is the table with the parts records

Transaction History is the table with the receipt records.

Here is the SQL for the query:

SELECT [Transaction History].MAXID AS SeqID, [Transaction History].PRTNUM_15 AS PartID, [Part Master].PMDES1_01, [Transaction History].TNXDTE_15 AS TDate, [Transaction History].TNXCDE_15, [Part Master].COST_01, [Transaction History].TNXQTY_15, DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "'and TNXCDE_15 = '" & "R" & "' and MAXID >= '" & [SeqID] & "'") AS RunTotal, [Part Master].onhand_01, IIf(Nz([leftSum],0)<=0,True,False) AS D, Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "'and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [SeqID] & "'")-[onhand_01]),0) AS LeftSum, IIf([RunTotal]<[Onhand_01],[TNXQTY_15],Abs([Leftsum])) AS AgeQty
FROM [Part Master] INNER JOIN [Transaction History] ON [Part Master].PRTNUM_01 = [Transaction History].PRTNUM_15
WHERE ((([Transaction History].TNXCDE_15)="R"))
ORDER BY [Transaction History].PRTNUM_15, [Transaction History].TNXDTE_15 DESC;

The query results all look good except for the LeftSum calculation and the AgeQty calculation and they are correct for all parts that have more than one receipt. If a part has only been received once, the calculations show zero.

How can I get this query to correctly age all receipts? Is this the best approach to my request? I adapted this approach from this post I found on Experts Exchange. https://www.experts-exchange.com/questions/27920676/Inventory-Aging.html

Doyce

__._,_.___

Posted by: winberry.doyce@con-way.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (9)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] Inventory Aging

Doyce,

Sorry about messing up your name. I need more coffee.

I would try move the Nz() to address only the DSum()

Nz(DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = 'R' and MAXID > '" & [MAXID] & "'"),0)-[onhand_01] AS LeftSum

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@con-way.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, September 10, 2019 9:13 AM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Inventory Aging

Hi Duane,

I'm not Art, I'm Doyce but your description is correct. Here is some sample data:

**qryInvAgingSelect**
MAXID	PartID	TDate	COST_01	TNXQTY_15	RunTotal	onhand_01	D	LeftSum	AgeQty
2792852	1001004	9/28/2016	12.6	1	1	3	-1	0	1
2248382	1001004	7/23/2014	12.6	3	5	3	-1	-1	1
2248716	1001004	7/23/2014	12.6	1	2	3	-1	-2	1
2248386	1001011	7/23/2014	2.7	8	9	7	-1	-6	6
2248723	1001011	7/23/2014	2.7	1	1	7	-1	0	1
2248387	1001012	7/23/2014	2.7	8	9	7	-1	-6	6
2248724	1001012	7/23/2014	2.7	1	1	7	-1	0	1
2785852	1001017	9/13/2016	10.8	2	2	1	-1	0	0
2785854	1001020	9/13/2016	24.05	2	2	1	-1	0	0
2785909	1001022	9/13/2016	2.25	4	4	2	-1	0	0
2785910	1001024	9/13/2016	1.5	2	2	1	-1	0	0

SELECT [Transaction History].MAXID, [Transaction History].PRTNUM_15 AS PartID, [Transaction History].TNXDTE_15 AS TDate, [Part Master].COST_01, [Transaction History].TNXQTY_15, DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "'and TNXCDE_15 = '" & "R" & "' and MAXID >= '" & [MAXID] & "'") AS RunTotal, [Part Master].onhand_01, IIf(Nz((Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MaxID] & "'")-[onhand_01]),0)),0)<=0,True,False) AS D, Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MAXID] & "'")-[onhand_01]),0) AS LeftSum, IIf([RunTotal]<[Onhand_01],[TNXQTY_15],Abs((Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MaxID] & "'")-[onhand_01]),0)))) AS AgeQty
FROM [Part Master] INNER JOIN [Transaction History] ON [Part Master].PRTNUM_01 = [Transaction History].PRTNUM_15
WHERE ((([Transaction History].TNXCDE_15)="R") AND (([Part Master].onhand_01)>0))
ORDER BY [Transaction History].PRTNUM_15, [Transaction History].TNXDTE_15 DESC;

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Hi Art,

Do you have some sample records and how you would want these reported?

Thanks,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, September 9, 2019 4:36 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Inventory Aging

Hi Rob,

No, that's not quite it. I need to know how long my inventory has been here.. If I have a part with more than one receipt, I need it divided by receipt date. Example part A has 50 on hand. I received 20 in May and 10 in July and 20 in August. I need to know that if Part A cost $1 that I have $20 of current inventory, $10 of 60 day inventory and $20 of 90 day inventory.

Thanks for taking time to answer.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <r.koelmans@...> wrote :

Hi Doyce,

If I understand right, your issue is whether what you still have on stock was purchased for the last time.

So you need a query that gives the last receiving date of items with OnHand_01 <> 0. That's a simple join on the two tables.

Next you use that query in a query that groups on month of the last purchased date. Unless I misunderstand what info is needed, that would do the job I think.

Rob

A couple suggestions:

Every time I read about a specification for "buckets" I create a table of "buckets" with MinBucketValue, MaxBucketValue, and BucketTitle.

Is MAXID numeric? You are using a numeric operator ">=" and yet comparing it with a string.

You are calculating LeftSum in the query and also using LeftSum in other expression. I never do this. I would repeat the LeftSum expression/calculation in place of using LeftSum elsewhere.

You do the same thing with SeqID. Why not use [Transaction History].MAXID?

I would always make sure there is a space prior to the "and". You have ..."'and TNXCDE_15....

This might or might not resolve your issue but I would sleep better.

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 4, 2019 2:54 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Inventory Aging

Hello Pros,

SeqID is an alias for MaxID which is the primary key in the transaction history table. It is autonumber.

TDate - Transaction Date (Receipt Date)

TNXCDE_15 - Transaction Code. "R" is an inventory receipt

Cost_01 - The cost of the part. Used to value the inventory.

TNXQTY_15 - The quantity of the part being received.

RunTotal - The sum of the parts received starting with the most recent receipt in descending date order

Onhand_01 - The total number of parts we currently have onhand

D - a Boolean as to whether this receipt should be included in the aging.

LeftSum - How many are left to allocate

AgeQty - how many are in this age bucket.

Part Master is the table with the parts records

Transaction History is the table with the receipt records.

Here is the SQL for the query:

Doyce

__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___