I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format
The format is 10 digits and it must start with a 0
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*") UNION SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
I'v filled the table with matches. And also calculated how many points every match has. (1 for draw, 3 for a win). So I have 306 matches, for a league with 18 clubs.
Now I want to make a query that makes a ranking based on points.
I have made two queries. 1. PARAMETERS Waarde Value; SELECT DISTINCT match.[Club A] FROM match WHERE (((match.season)=[Waarde]));
Which lists the 18 clubs distinctively in the season.
2. PARAMETERS Waarde Value, Club Text ( 255 ); SELECT Sum(match.points) AS SumOfPoints FROM (Clubs AS CA INNER JOIN match ON CA.ID = match.[Club A]) INNER JOIN Clubs AS CB ON match.[Club B] = CB.ID WHERE (((match.[goalsA])>[GoalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])>[goalsA]) AND ((CB.[Club naam])=[Club])) OR (((match.[goalsA])=[goalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])=[goalsA]) AND ((CB.[Club naam])=[Club]));
which gives me the total points of a club in a league if I input the season with the value Waarde and the Club with the value text.
How do I combine these two. For every resultrow of (1) I want the result of (2) presented next to it.
I'v tried these two queries and they both seem to work okay.. but how do I combine them ?
I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. Basically I need a list that has 5800 accounts, shows who has a phone number and who does not have a phone number. I have made a relationship between the two tables based on the person’s name. I used the code below and put the code in the “criteria” and “name” block. However, I received no results. When I remove the code from the query I receive only the results from list 2 (2300 accounts) and no data from list 1. Any Ideas?
FROM [5480 Master Data] INNER JOIN [5480 Meal Collection] ON [5480 Master Data].name=[5480 Meal Collection].name
I am really struggling with this, but I need to combine these two queries. They count the number of goals scored and conceded in a fixture between two teams to give the games played, games won lost and drawn and Goals for and against and points tally.
However, I can only get it to work for the Hometeam. The second query gives the results for the Away team, but I need to combine the two together to give one league table with all the results and points etc combined.
Hope you can help :o
This displays the home team results and calculates everything correctly.
SELECT tbl_Fixtures.TEAMID, TBL_TEAMS.NAME, COUNT(*) AS GAMESPLAYED, SUM(IIf((HOMEGOALS>AWAYGOALS),1,0)) AS GAMEWON, SUM(IIf((HOMEGOALS=AWAYGOALS),1,0)) AS GAMEDRAWN, SUM(IIf((HOMEGOALS<AWAYGOALS),1,0)) AS GAMELOST, SUM(HOMEGOALS) AS GOALSFOR, SUM(AWAYGOALS) AS GOALAGAINST, SUM((IIf((HOMEGOALS>AWAYGOALS),3,0))+(IIf((HOMEGOALS=AWAYGOALS),1,0))) AS POINTS FROM tbl_Fixtures, TBL_TEAMS WHERE tbl_Fixtures.TEAMID=TBL_TEAMS.TEAMID GROUP BY tbl_Fixtures.TeamID, TBL_TEAMS.name;
This displays the Away team results and calculates everything correctly.
SELECT tbl_Fixtures.opponentID, TBL_TEAMS.NAME, COUNT(*) AS GAMESPLAYED, SUM(IIf((AWAYGOALS>HOMEGOALS),1,0)) AS GAMEWON, SUM(IIf((AWAYGOALS=HOMEGOALS),1,0)) AS GAMEDRAWN, SUM(IIf((AWAYGOALS<HOMEGOALS),1,0)) AS GAMELOST, SUM(AWAYGOALS) AS GOALSFOR, SUM(HOMEGOALS) AS GOALAGAINST, SUM((IIf((AWAYGOALS>HOMEGOALS),3,0))+(IIf((AWAYGOALS=HOMEGOALS),1,0))) AS POINTS FROM tbl_Fixtures, TBL_TEAMS WHERE tbl_Fixtures.opponentID=TBL_TEAMS.TEAMID GROUP BY tbl_Fixtures.opponentID, TBL_TEAMS.name;
Now I just have to figure out how to combine the two, which is proving more difficult than I thought it would be. My SQL isn't the best :o
I'm trying to create a combo box that would show the next ID number available to name a piece of equipment, as well as any numbers in the range that can be recycled.
This is in an equipment database where the equipment is named using the following convention: Division - equipment type - #. For example, a PC would be: DIV1-CPU-0001. My current drop down box generates the next number (so you can see the next ID for DIV1-CPU-0002). It's just a MAX+1 query.
However, if we have 10 PCs and DIV1-CPU-0008 was stripped and sent to recycling, then 8 is available as an ID number. I want my drop-down box to show, in that case, "8" and "11" as the options.
I've created a table with all the numbers and created queries that show which ID numbers aren't in use for each type of equipment. However, I can't figure out how to put all this in one query for my combo box.
I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id. Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables. As i am dutch my english isn't that good. Thanks in advance
I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.
I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.
I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.
For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...
So at the moment i have two queries per responsibility, and one pulling those two together i.e.:
New Work Items: SELECT Count(ticketsds.id) AS [DS New] FROM TicketsDS WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth)); Closed Work Items: SELECT Count(ticketsds.id) AS [DS Closed] FROM TicketsDS WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));
Then i have this query pulling the two results together: SELECT DSClosed.[DS Closed], DSNew.[DS New] FROM DSClosed, DSNew;
Is there anyway of combining the first two queries into one, rather than have 3??
Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....
Many Thanks! Gareth
PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...
I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.
The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).
I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).
(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)
SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder, Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete FROM Suppliers INNER JOIN (OrderDetails INNER JOIN (((Customers INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID) INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account) INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation) ON OrderDetails.Index = SavingsClubReservations.OrderID) ON Suppliers.Index = OrderDetails.Supplier WHERE (SavingsClubAccounts.Redeemed=False) ORDER BY Customers.Name
I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.
SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False) And SavingsClubAccounts.Account Not IN (SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False)) UNION (SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));
Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...
I was trying to combine some fields into one with a search query,using sql. I was going to search for a postcode then add the first two lines of an address (which are at present in 2 fields) together seperated by commas ",". For instance:
Strd 12 Rock View Str Marston Cresent
change to :
12 Rock View, Marston Cresent
I can add fields together with an expression but cannot divide them by commas, any help appreciated.
I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.can I combine the 2 SQL statements in the 2nd query without amending them in any way.
The SQL statements are;
FROM [Mail List] AS a GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1] HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1] From [Mail List] as b Where b.[Address 1] = a.[Address 1] Group By [Address 1])));
I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?
I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.
the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.
i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it.
I have created a booking system for a travel agency and now when the booking is complete I want to export some summary data to the Excel. The issue is that I have separate tables showing lunch, tickets, tour guides, etc for every destination and I have also created queries showing the total for each destination but the issue is that I cannot combine all the results into one query and export that query to excel.
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case. Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler) 2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").
Using Windows 7, Access 2010
Is there a way to select multiple values from the drop downs?
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs JobID (J000001, J000002, etc) Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc Consultant: (Joe, Terry etc) ObjectID: (RoberI, SmithJ etc) Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001" as [JobNo] FROM J000001 UNION SELECT ObjectID, Consultant, Status, "J000002" as [JobNo] FROM J000002 UNION SELECT ObjectID, Consultant, Status, "J000003" as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual FROM (
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper. Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual FROM [
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable Now when you want to execute the query you get an errormessage. Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?
