I have a table of UPCs that we use for the tee shirts we manufacture. The problem is that our MRP system organizes the tables with S,M,L,XL UPCs in one row and the XXL UPCs in a seperate row(we use different pricing for XXL). I need to figure out how I can make it so that there is only one row per style number taht has both the S,M,L,XL,XXL UPCs in one row, but can't seem to figure out how to do this. I've attached in image for reference.
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3 V.I. Capital Appreciation Fund.................5 V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3, 5 ,1
Hi I currently have 2 seperate reports that I want to use in one. I have...
1. A Date report that works from a form with a to and from field, it then finds all reports between those two fields.
2. A Client report from a form, a simple drop down box that gets it info from a query, it then works of a macro to find all records to that client
What I want is to have the one form where you can 1st select the client at the top from the drop down and then you enter the to and from date, once you click ok it will bring up all records for that client within the dates.
This is the code I use for the dates, is there a way to add an extra bit that makes it look at the client combo as well to just show the records for that client between the specified dates...Code:Private Sub OK_Click()Dim strReport As String 'Name of report to open.Dim strField As String 'Name of your date field.Dim strWhere As String 'Where condition for OpenReport.Const conDateFormat = "#mm/dd/yy#"strReport = "clientnameanddate"strField = "DateJobReceived"If IsNull(Me.txtStartDate1) ThenIf Not IsNull(Me.txtEndDate1) Then 'End date, but no start.strWhere = strField & " <= " & Format(Me.txtEndDate1, conDateFormat)End IfElseIf IsNull(Me.txtEndDate1) Then 'Start date, but no End.strWhere = strField & " >= " & Format(Me.txtStartDate1, conDateFormat)Else 'Both start and end dates.strWhere = strField & " Between " & Format(Me.txtStartDate1, conDateFormat) _& " And " & Format(Me.txtEndDate1, conDateFormat)End IfEnd IfDebug.Print strWhere 'For debugging purposes only.DoCmd.OpenReport strReport, acViewPreview, , strWhereEnd SubPS I didnt write this code i just edited it for my own use so please reply in simple terms
I have a database that uses a field for the year (but I'm using a fiscal year that will end June 30, 2013) and another field as an autonumber. I use the year and autonumber as my reference number (i.e 2013-0001). I'd like to be able to combine these fields to generate the entire number. Also, I'd like for the year to add 1 beginning July 1st of each calendar year, and the autonumber start over at 0001 (i.e. 2014-0001 on July 1, 2013). Is this possible, and if so, how can I do it?
im having some problems with my db. i have 2 tables(ATL and BATE) in ATL, i have (im, IMAGEID, BREAK, TASK, LOC, bate) and in BATE, i have (Bate, var)
im trying to take Bate from Batetable, and insert it to ATL, even tho thers already a bate in ATL, both contain different records... i tried this, but didnt work Select ATL.im, ATL.IMAGEID, ATL.break, ATL.task, ATL.loc, ATL.Bate, BATE.bate from ATL, BATE; it worked, but BATE shows the same value for the hole row, it displays the same thing thing, even tho each row has a different value... can anyone help me out? thanks
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectB UserA - Hours - Date - ProjectB UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.
I maintain an Access database at my workplace, and have been asked to bring in information from other company locations. Unfortunately, there are conflicts; e.g. part number 12345 might be something totally different at another facility than it is here. My first instinct was pretty obviously to add a Location column and all queries would include that as well as the ID. However, someone else is telling me we should build metadata into a new part numbering convention. An example:
PlantA.12345 (all one field) would be a totally different part than PlantB.12345
He says it would be very beneficial to ensure that, seeing only a part number, you would know where it was from. I understand his point, but it just seems like strange database design.
Has anyone done something like this, or considered it??
I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?
Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.
I'm modifying a database for someone in our marketing department. The DB tracks shirts that are given to employees.
The area I'm having a problem with has to do with the shirt style.
The current categories offer:
Long Sleeve (White) Long Sleeve (Black)
etc..
I want to combine those two so they fall under the simple category "Long Sleeve"
The problem I'm having is that when I do a find/replace, it will allow me to change all of the Long Sleeve (White) to Long Sleeve, but then when I go to change Long Sleeve (Black) to "Long Sleeve", it creates duplicate entries because the same order contained x amount of Long Sleeve (White) and x amount of Long Sleeve (Black).
Is there an easy/ automated way to combine these results into "Long Sleeve" and add the quantity fields together to show the total amount of shirts ordered?
Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.
I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.
SQL Statements
FIRST QUERY
SELECT tblApp.Time FROM tblApp WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));
SECOND QUERY
SELECT tblTime.Time FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));
The image above shows results from a query I've created. However, I need to group the results by Product Id and display a sum of the individual Product Id's. So I should have something like:
Commercial Loan Mr Steve F Cobblestone LNCLCLRE £299,491.33 Demand Deposit Mr Steve F Cobblestone DD033 £7,402.81 Demand Deposit Mr Steve F Cobblestone DD043 £3,929.78 Time Deposit Mr Steve F Cobblestone TD013 £12,195.60 Time Deposit Mr Steve F Cobblestone TD018 £961,924.12 Time Deposit Mr Steve F Cobblestone TD025 £113,483.58
Any ideas how I construct a query to display the results as per above??
Name______________Colors Chosen Bill..............................Red, Blue, Green Tom............................Red, Blue Mark...........................Green
Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.
the first quierie contains like 6 records and the second one contains 3 records
the problem is is that when combining these columns into the one query the second contains more then 3 records when it should be only displaying data for the 3 bits ?
:confused: I need help desperately. I have two tables I am trying to comine in a query. Seems easy enough right? Yet the simple task has become impossible. I have two tables with the same feild for data, both containing 8 coulmns. Ex:
Table A-January Columns A B C Data: 1,2,3
Table B-February Columns A B C Data: 4,5,6
I'm trying to create a query that will contain the data 1,2,3,4,5,6 but am getting 11111111111,222222222,33333333333,4444444444,55555 55555,6666666, or getting 1,3,5. I've tried refining the data in many ways, even tried just created a table. Hand keying it in seems rediculous due to the end numbers being over 10,000. Any advice??? Please??? :(
I seem to have a similiar problem to others and I tried to read through this forum, but I am not well versed in the code and I frankly find some of the explanations way to complicated. I was hoping someone can help me with a problem. I have data that looks like this:
Name Comments J Smith 1/1/07-ATO J Smith 1/10/07-UAU S Smith 1/13/07-ATO D Smith 1/2/07-OT D SMith 1/3/07-OT D Smith 1/4/07-ATO
I want it to look like this:
Name Comments J Smith 1/1/07-ATO, 1/10/07-UAU S SMith 1/13/07-ATO D Smith 1/2/07-OT, 1/3/07-OT, 1/4/07-ATO
I just had to pull a large amount of data from an off-site SQL server. Due to speed issues, I broke up the data into 3 pulls and exported the data as text.
I have now imported the data into Access and wish to combine the three tables into one. All of the fields are the same, the only difference is that I broke up the pulls by date ranges.
Would this be done via an Update or Append query? AND, how would that query look? Sorry, still a bit new to this. :)
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 ?
In a query design view, I have two fields, LastName and FirstName. In the Field of a blank column I enter [LastName]&[FirstName]. In the query the last and first names are now connected , like SmithJohn. How do I put a space and or a comma to separate them?
What is the best way to go about tackling this?? I need to create this in a table so that I can export it from Access and into a .dbf to be used with another program.
I've read through the other threads but can't seem to find anything that is simple (not an experienced SQLer so can't really tweak too much) and that would work for my problem.
I have a Project table linked to an Invoice Table (1 to many respectively)
The Invoice table linked to a Transaction table (1 to many respectively). 1 invoice can be paid out to many different cost centers and amounts.
I also have a Reclass table that is linked to the Project table.
Basically Invoices can be processed to multiple cost centers originally. But can also be reclassed at a later date to other cost centers.
The way the database is setup is so that if there are 10 invoices each having multiple records in the Transaction table (charging 1 invoice to multiple cost centers) AND we need to reclass them. We don't want to reclass each an every invoice and transaction record. It setup so that we can take the total invoiced amount for the project (sumed up all the invoices for that project) and reclass one total amount.
The problem is now I have 2 separate "financial" tables. One for the original invoice payment transaction (Transaction table which has the original cost centers and amounts) and a Reclass table with the "new" cost centers and amounts.
I need to combine them so that the financial reporting will reflect the reclasses (moving dollars from one cost center to another).
I tried creating a separate table (All Financials Table) that I can append the "original" Transaction table and Reclass table to use for reporting. I have this working but I can forsee possible problems going forward (i.e. user may append multiple times by a button click).
Is there a simple way to do a combined query on both the Transaction and Reclass table. Both queries have the following: Project ID, Cost Center and Amount. The problem I'm having is that if one table has 10 records of say cost center 123456 and the other has 3 records of 123456. The query ends up giving me 30 records.
I know my explanation is long but I think it will help paint the bigger picture.
I have a query called Spans1 that calculates the difference between dates:
SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, [Rasp].Date_Received, [Rasp].Date_Resolved, DateDiff("d",[Date_Received],[Date_Resolved]) AS Span FROM [Rasp] WHERE ((([Rasp].Status) Like "*" & [Enter status]));
I have another query called Average_Span that gives me an overall average number of days between the dates in the above query:
SELECT Format(Avg([Span]),"0.0") AS Expr2 FROM Spans1;
Is there a way to combine the 2 so that I get the spans and an overall average? Any help is greatly appreciated.
