UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Builder Formula, Access 2016    
 
   
jermarie
post Mar 21 2020, 09:43 AM
Post#1



Posts: 1
Joined: 21-March 20



Hi guys,

Need help cause im confuse on how to get this
I have 5 groups
Group A,B,C,D,E on my data.THe data is from Jan l to Feb29 however I need to cut the data for group B and C,it should only show from Jan 1 to 15 then the rest is until Feb29.
Is there a formula i can use for that??
Go to the top of the page
 
Jeff B.
post Mar 21 2020, 10:12 AM
Post#2


UtterAccess VIP
Posts: 10,411
Joined: 30-April 10
From: Pacific NorthWet


"How" depends on "what" … what is the (table) structure of the underlying data?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Mar 21 2020, 10:29 AM
Post#3


UA Admin
Posts: 37,015
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Please provide more details, as Jeff suggested.

Start with the ACTUAL values. Include ACTUAL sample data, so we can see what is involved.

Explain how you define a group. Why do you need to segregate data by date?

All of these factors are involved in the solution.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
DanielPineault
post Mar 21 2020, 10:30 AM
Post#4


UtterAccess VIP
Posts: 7,266
Joined: 30-June 11



Please give us a concrete example of your existing data and desired output. (obviously omitting any confidential information)

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Bullschmidt
post Mar 25 2020, 10:13 PM
Post#5



Posts: 31
Joined: 8-May 19
From: USA


QUOTE
Need help cause im confuse on how to get this
I have 5 groups
Group A,B,C,D,E on my data.THe data is from Jan l to Feb29 however I need to cut the data for group B and C,it should only show from Jan 1 to 15 then the rest is until Feb29.
Is there a formula i can use for that??

In design view of a query the criteria can have more than one row.

o So in the first row of criteria you could have:
- Group field criteria: "B" Or "C"
- Date field criteria: Between #1/1/2020# And #1/15/2020#

o And in the second row of criteria you could have:
- Group field criteria: "A" Or "D" Or "E"
- Date field criteria: Between #1/1/2020# And #2/29/2020#

Then the query's resulting recordset would show all B and C records from 1/1/2020 to 1/15/2020 and all A, D, and E records from 1/1/2020 to 2/29/2020.
This post has been edited by Bullschmidt: Mar 25 2020, 10:19 PM

--------------------
J. Paul Schmidt - Freelance Web and Database Developer
Sample Database on the Web
Sample Access Database

Go to the top of the page
 
arnelgp
post Mar 26 2020, 12:07 AM
Post#6



Posts: 1,528
Joined: 2-April 09
From: somewhere out there...


create a Union Query.
the resulting recordset is not updatable.
CODE
select field1, field2[, fieldN] from yourTable where [group] IN ('B', 'C') And [dateField] Between #1/1/2020# And #1/15/2020# ORDER BY [group], [dateField]
union
select field1, field2[, fieldN] from yourTable where [group] NOT IN ('B', 'C') And [dateField] Between #1/1/2020# And #2/29/2020# ORDER BY [group], [dateField]


--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
cheekybuddha
post Mar 26 2020, 05:34 AM
Post#7


UtterAccess Moderator
Posts: 12,630
Joined: 6-December 03
From: Telegraph Hill


Maybe more simply:
CODE
SELECT
  *
FROM YourTable
WHERE ([group] IN ('B', 'C') AND [dateField] >= #2020-01-01#  AND [dateField] <= #2020-01-15#)
   OR [group] NOT IN ('B', 'C')
;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Jeff B.
post Mar 26 2020, 12:01 PM
Post#8


UtterAccess VIP
Posts: 10,411
Joined: 30-April 10
From: Pacific NorthWet


Again, without some idea of how your data is structured (i.e., table/fields), we'll need to assume your data is well-normalized and offer "how to's" based on that assumption.

More info, please...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th April 2020 - 07:11 PM