I've created an accounts package for my business using access 2003 and I'm just thinking of some time saving features.
We need to email our invoices to our clients.
Normally we print out an invoice, scan it back into the computer, save as a PDF and attach to an email manually one by one.
My package can now print to PDF (saving one step), but what I would like is to be able to print to PDF and attach to a new email (with the email address coming from the customer table) and possibly attach more than one PDF to a single email.
I know you can use
Code: DoCmd.SendObject acReport, stDocName
to send a report but I would like it as a PDF as my formatiing is lost using that code and we normally send PDFs to our clients.
I'm new to MS Access and these group forums, and have just taken over an admin role, so please bear with me.
I'd like help with the following. Just nearly had a catastrophe with MS Access, but luckily I've managed to get it back to what it was this morning.
We have a Task Database, set up years ago, which is held on a centralised server, and each person accessing the database has a folder called C:/Task, with a Task.mdb icon, which then links to the main database. This works OK.
I also have a database on another server, which is used for logging problems, and handovers. I've set this up as a One stop shop, with a nice front view, from which I have links to all our databases, and other documents, one of which is the Task Database. This worked fine until this morning.
What I was attempting to do was to setup access to that it recognises the person logging on, and instead of saying Hello Admin, it will say Hello xxx.
I tried setting up a new multi user environment, by updating the Workgroup Administrator. I set up a new .mdw file called system.mdw, which worked on my task database, when I logged on directly.
However, when I log on via the front database, it won't open up the Task Database, as it says that the system.mdw file is in use.
Is it possible to set Access up, so that you can use logon account on more than one DB ???
I've now reset the Workgroup Administrator to point back to WINNT/system.mdw, and it now allows me to access the task db from my front db... So, I'm back to square one.
I moved my access database and not my user account do not have privelages. However, the group accounts they are linked to have been set-up correctly. Why would my group account privelages be ok, but when I look at each user individually they have no privelages set. I'm confused as to why this would be if the users are in the specific groups I have set-up.
Using Access's User and Group Accounts or Using a Login Form to access database?
I've been researching on how to make a database secure. How to create User and Group Accounts on access, I see the step by step instructions and tried it out myself.
I also saw some sites where they give an example of a Login Form and how to create one.
My question is do you need to create both. First create the users and groups to permit or deny access to certain forms and then have a login form?
But would that mean that they'd have to login twice? Once when the database opens because it activates the db security that was created and then login again in the login form that was created?
Also when the user logins in and clicks on the cmd button on the form which opens up another from, frmWorkLog, I have an Employee field. This field I want it to have the user's name entered automatically and "locked". So that info, employee name, is extracted from the user's login. So then the user can only see his or her records only and no one elses.
How would I go about creating that. Hope I made my explanation clear.
I have a few tables, one of which stores a record of changes made to user accounts, and some of those changes are linked to a "multichange" table for when I have made the same change to multiple accounts.
What I am trying to do is select the change_id from my changes table, with a limit of 1 as I only want one result (it will always be the same) but I am using this query and getting a syntax error telling me I have a missing operator.
Code: SQL_SELECT = "SELECT change_id from dbo_user_change where username = '" & username & "' AND change_type = 'new account' LIMIT 1" Set qresults = CurrentDb.OpenRecordset(SQL_SELECT) Do While Not qresults.EOF multichange_id = qresults.Fields(1) qresults.MoveNext Loop qresults.Close
I have a query which looks at a linked table (ODBC to SQL Server) in order to return a list of sub-accounts associated with an account. If I run it as a pass-through query it executes correctly, returning a list of the sub-accounts. If I run it as a query in Access it return the correct number of records but each sub account has the same name as the main account, as follows.
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890 ABE12345 ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator FROM MatterContactsMade GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator])) ORDER BY MatterContactsMade.MatterId;
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.
Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.
The both query details:
Customer Credit Transaction Final SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount] FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID WHERE (((BillBook1.BillMode)="Credit")); UNION ALL
[code]...
Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.
SELECT DISTINCTROW tbl_members.surname, Count(tbl_years.year) AS CountOfyear FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member GROUP BY tbl_subscriptions.subscription_fee, tbl_members.surname HAVING (((tbl_subscriptions.subscription_fee)=0));
This query displays a list with the surname of the member and the Count of the Years he/she did not pay the annual subscription, hence where subscription_fee = 0
TRANSFORM Sum(tbl_subscriptions.subscription_fee) AS SumOfsubscription_fee SELECT tbl_members.surname, tbl_members.name, tbl_members.mobilephone FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member WHERE (((tbl_years.year)>Year(Date())-"6")) GROUP BY tbl_members.surname, tbl_members.name, tbl_members.mobilephone PIVOT tbl_years.year;
This query displays a list with the surname, name, mobile phone of the member along with the money he/she paid for the last 5 years as you can see from WHERE (((tbl_years.year)>Year(Date())-"6"))
My question is: is it possible to combine those 2 lists and have one where all of the following columns will be listed? Surname, Name, Mobilephone, Count of years with 0 payment, a column for each year of the last 5
I have two queries that I am interested in combining into one if possible. I'm trying to learn Access and SQL on-the-fly, so feel free to point out any noob mistakes I am making.
The first query simply pulls certain records from a table:
SELECT Sensor5.LaneName, Sensor5.SensorTime, Sensor5.Speed, Sensor5.Volume FROM Sensor5 WHERE (((Sensor5.LaneName)="NB1" Or (Sensor5.LaneName)="NB2" Or (Sensor5.LaneName)="NB3") AND ((Sensor5.Volume)>0) AND ((Sensor5.SensorDate)="4/17/2007" Or (Sensor5.SensorDate)="4/18/2007" Or (Sensor5.SensorDate)="4/19/2007" Or (Sensor5.SensorDate)="4/20/2007" Or (Sensor5.SensorDate)="4/23/2007" Or (Sensor5.SensorDate)="4/24/2007" Or (Sensor5.SensorDate)="4/25/2007" Or (Sensor5.SensorDate)="4/26/2007" Or (Sensor5.SensorDate)="4/27/2007"));
The second query then takes averages and sums from this first query, grouping by a third field (SensorTime). This results in weeks of data being compiled into a single record for each time interval in a 24-hour period. See below:
SELECT SpeedWeekday5NB.SensorTime, Avg(SpeedWeekday5NB.Speed) AS AvgSpeed, Sum(SpeedWeekday5NB.Volume) AS SumVolume FROM SpeedWeekday5NB GROUP BY SpeedWeekday5NB.SensorTime;
Is there any way I can streamline this process by combining the two queries into a more complex single query, or should I leave things as-is? Any advice is much appreciated!
I would like to take these two queries and combine them into one if possible. This is the first query:
SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1] FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));
And this is the second query which is based on the results of the first query:
SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2] FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0)) GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
Can this be done and if yes can someone show me how? Thanks
Hi~! I'm new in access vba and sql... hopefully my question won't be too dumb.
Basically i'm working on a report... which the report data/information was calculated in few different queries(which i created) from few different tables. in simple, each of the data/information was from a query... i got about 10+ data/information to display, that's why i created 10+ queries.
I done all the calculation in queries, but my problem is... how to add all these queries into 1, so i can directly create a report from the 1 query.
most of the queries was using union all/union... i tried to use union/union all to put all these queries together, but it won't works... because each of the queries using different tables/fields... even though i add those table/fields into the queries... it didn't work as well...
Code:SELECT DISTINCT Team.TeamLeadName, RawData.OutlineNumber2FROM RawData LEFT JOIN Team ON RawData.TeamLeadNumber = Team.TeamLeadNumber;
Code:SELECT RawData.OutlineNumber2, FormatPercent(Avg((RawData.Finish-RawData.Start)/(SELECT SUM(RawData.Finish - RawData.Start) FROM RawData))) AS DaysSpannedProgressFROM RawDataGROUP BY RawData.OutlineNumber2;
the output of query 1 is TaskName | Outlinenumber2
the output of query 2 is TeamLeadName | Outlinenumber2
and the output of query 3 is Outlinenumber2 | DaysSpannedProgress
I would like to have one query that has all that info... TeamLeadName | Outlinenumber2 | TaskName | DaysSpannedProgress
I have ONE table with data that looks like: Username1, Username2, Date, EventType, Money (basically)
I have a query that combines all the Username1 entries together. It sums the money, and COUNTS the times it was entered into the database.
I have another query that does the same, but uses Username2 as the main filter.
The reason I have the two queries, is that not every entry in the main table has a Username2 in it. But I want to make sure I include those entries that have a Username2 in the totals and counts.
Im not sure if this makes sense to anyone, but if there is a way to combine the two queries I have, it would help to have some guidance. I don't have a clue how to do it in ASP (if possible) and don't know what to try inside ACCESS as I experiemnt around.
Thanks for any help or guidance. I am quite the newbie to ASP scripting. Thanks again.
Product. Item Description A It's Red B It's Blue C It's Green D You'll love it
Category. Item Category_name A Cat1 A Cat2 A Cat3 B Cat1 B Cat4 C Cat1 C Cat6
I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:
ItemCategoryConsolodation. Item Desc Categories A It's Red Cat1,Cat2,Cat3 B It's Blue Cat1,Cat4 ...
How does one do that?!
What's it called when you try to do this so I can Google it?
I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?
