Combine records in a query (1 Viewer)

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Hello Friends

I have to create a Transmittal to send some drawings to our Subcontractor. Now what I do.... when I press AddNew Button, it work like this....

Main form open with fields.....

User.... = by selecting from combo box
Today's Date = Automatically toddays
Receiving Department = By selecting Combo Box
Job Completion Date = Automatically Date + 30 days

Now I have a subform and I select Drawings by Checkbox.... Then Save this record and Print... This every thing is OK...

Now I want to create an other record.... and I press Addnew button.... previously selected records still selected...

I want when I press AddNew button.... the selected records in Subform should be Uncheck / Clear...

Kindly help me how to do this.... Checkbox name is "ForPrint"
Thanks
irsmalik
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:45
Joined
Sep 21, 2011
Messages
5,098
Re: Multi-Select Listbox as filter for Query field

Best to start a new thread?

EDIT by isladogs 21/12/2019
I've just moved the OP's question and the responses to a new thread
 
Last edited by a moderator:

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:45
Joined
Aug 30, 2003
Messages
33,790
Re: Multi-Select Listbox as filter for Query field

Since it sounds like you're using a field in the data, run an update query that sets that field to false.
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Re: Multi-Select Listbox as filter for Query field

Sir Pbaldy... thanks for your reply...

I am not using any query.... In my Subform.... I am selecting required records direct from Table.

Now how to un-check it. ?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:45
Joined
Aug 30, 2003
Messages
33,790
Re: Multi-Select Listbox as filter for Query field

I didn't say you were using a query, I said you could. To reset all records:

CurrentDb.Execute "UPDATE TableName SET FieldName = False", dbFailOnError

perhaps more efficient if there are a lot of records


CurrentDb.Execute "UPDATE TableName SET FieldName = False WHERE FieldName = True", dbFailOnError
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Re: Multi-Select Listbox as filter for Query field

Sir Pbaldy... thanks for your reply...

I already mentioned that Checkbox is in my Subform.... Now I pasted below line in AddNew Button.

CurrentDb.Execute "UPDATE TableName SET FieldName = False", dbFailOnError

it is working.... and it clear all the checkbox but Only when after pressing Addnew Button.. and then click on any line in subform.... all the checked box become un-checked.....

My required task is completed... but is this correct method to press any other key. ?

Thank you sir PBadly....
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:45
Joined
Aug 30, 2003
Messages
33,790
Re: Multi-Select Listbox as filter for Query field

You could requery the subform after executing the UPDATE query.
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Re: Multi-Select Listbox as filter for Query field

Hello Friends

For me, it it a difficult question but hope there will be an easy solution. From query, I am getting details of Farmers meetings data i.e.

Company Region Territory DTP TDate Periods

Here is the query:

SELECT DM.CName AS Company, DM.RName AS Region, DM.AreaName AS Territory, Sum(DM.DTP) AS SumOfDTP, DM.TDate, Format([TDate],"mmm") AS Periods
FROM DM
GROUP BY DM.CName, DM.RName, DM.AreaName, DM.TDate, Format([TDate],"mmm")
HAVING (((Sum(DM.DTP))>0))
ORDER BY DM.CName, DM.RName, DM.AreaName, Sum(DM.DTP) DESC , DM.TDate, Format([TDate],"mmm");

Query gives me 5 records data wise... I want this results in One Records which gives me Months name with Comma Like (Jan, Feb, Mar etc). Please see the attached picture for more clarification.

Hoping for help... thanks
irsmalik
 

Attachments

isladogs

CID Moderator
Staff member
Local time
Today, 09:45
Joined
Jan 14, 2017
Messages
12,567
@irsmalik
Your question was 'piggybacked' onto an existing old thread to which it was unrelated.
I have therefore moved your question to a new thread together with the responses.
 

theDBguy

I’m here to help
Local time
Today, 01:45
Joined
Oct 29, 2018
Messages
8,713
Re: Multi-Select Listbox as filter for Query field

Query gives me 5 records data wise... I want this results in One Records which gives me Months name with Comma Like (Jan, Feb, Mar etc). Please see the attached picture for more clarification.

Hoping for help... thanks
irsmalik
Hi. Here's another option: SimpleCSV().


Hope that helps...
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Hello friends .... Here is a CrossTab Query

TRANSFORM Sum(DM1.BG) AS SumOfBG
SELECT DM1.Company, DM1.Region, DM1.Territory, Format([TDate],"mmm") AS Period1, Sum(DM1.BG) AS Total
FROM DM1
WHERE (((DM1.BG)>0))
GROUP BY DM1.Company, DM1.Region, DM1.Territory, Format([TDate],"mmm")
PIVOT DM1.BG;

it gives me data given below. Kindly see the attache pic for more clarification.

Company Region Territory Total Period1
GREENLET BAHAWALPUR KAROR PAKKA 1 Aug
GREENLET BAHAWALPUR LODHRAN 3 Jul
GREENLET BAHAWALPUR YAZMAN 1 Jul
GREENLET DG KHAN CHOTI Z 1 Aug
GREENLET DG KHAN D.G KHAN 1 Apr
GREENLET DG KHAN D.G KHAN 1 Aug
GREENLET DG KHAN D.G KHAN 1 Jul
GREENLET DG KHAN FAZAL PUR-II 1 Aug
GREENLET DG KHAN FAZAL PUR-II 1 Jul
GREENLET DG KHAN JAM PUR 1 Apr
GREENLET DG KHAN JAM PUR 1 Feb
GREENLET DG KHAN JAM PUR 1 Jan
GREENLET DG KHAN KOT ADDU 3 Aug
GREENLET DG KHAN KOT ADDU 2 Jul

If my query showing 3 or 4 records per Territory, I want to display it in One Record. as per attached pic.

You all are requested to help me.
Thanks in advance
irsmalik
 

Attachments

isladogs

CID Moderator
Staff member
Local time
Today, 09:45
Joined
Jan 14, 2017
Messages
12,567
@irsmalik
Your last post is effectively the same request as post #8.
There were two suggested solutions in posts #9 & #11, neither of which you acknowledged.
Please try one/both of those and report back on how you get on
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Thanks for reply.


I tried... but could not understand / follow the suggestions.

I want some query through which I could do my assignment.

irsmalk
 

theDBguy

I’m here to help
Local time
Today, 01:45
Joined
Oct 29, 2018
Messages
8,713
Thanks for reply.


I tried... but could not understand / follow the suggestions.

I want some query through which I could do my assignment.

irsmalk
Hi. If you use my simple function, you could use a query like the following:
Code:
SELECT Company, Region, Territory, Sum(Total) As SumOfTotal,
SimpleCSV("SELECT Period1 FROM DM1_Crosstab1
 WHERE Company='" & [Company] & "' AND Region='" & [Region]
 & "' AND Territory='" & [Territory] & "'") AS Period1
FROM DM1_Crosstab1
GROUP BY Company, Region, Territory
Make sure to copy and paste the SimpleCSV() function from my website into a Standard Module in your project and leave the name of the module as Module1.


Hope that helps...
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
Hello friends

In questions topics, I could not find Excel. So I am asking my question here.

I know how to create Hyperlink in Excel workbook. But I want to give my data to an other person, hyperlinks will work only if the file/ path should be same.
In our company we keep the data of our products with their Registration Details and this Registration is in PDF in my D Drive with a Folder Name “PDF Files” with hyperlink, so I can open it easily.
This data is shared with my Boss every month. I copy Excel File + PDF folder to his PC. He can open the excel file and can see the Registration pdf file.
My boss always ask me if he copy this folder in C, D or E drive, hyperlink will Not work. So I must find a solution, that we should not specific a Drive for hyperlink. So my question is:….
Is there a way that we may not mention a specific path. Hyperlink search my required file in Computer (C, D or E drive) and then open it by clicking. ?

Thanks in advance.
irsmalik
 

theDBguy

I’m here to help
Local time
Today, 01:45
Joined
Oct 29, 2018
Messages
8,713
Hi. I've never tried it in Excel before, but maybe try replacing the drive path with the file's UNC address, if all users are on the same network.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:45
Joined
Sep 21, 2011
Messages
5,098
Can you not just put it somewhere where you both have access, like a server folder?

Why didn't you start a new thread for this question as it does not appear to be related to the original question?
 

irsmalik

Registered User.
Local time
Today, 12:45
Joined
Jan 28, 2015
Messages
51
First, I don't know how to start new thread. So I just kept my question here.

Second, my boss use laptop and he want to keep a copy of all the record in his own custody. We don't use server for this kind of data.

Hope any solution will be found. thanks
irsmalik
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 03:45
Joined
Feb 28, 2001
Messages
16,253
irsmalik, if your boss is keeping this on his laptop, fine. But he will have to grant appropriate file permissions to all users who would want to get into the data. Just remind him of that fact. And, his laptop will have to be on during the times when others will need to access it, so there is the issue that he cannot have it shut itself down on a timer during business hours. Which will shorten the life of his laptop.

Having said that, it is his choice to do things that way. Advise him and move on to the next part of the problem.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom