multi-value field

I'm still learning Access, so I must apologize in advance for my question. 
I have four tables, tblBrochure, tblStaff, tblOrganization, 
tblResourceDistribution.

tblBrochure has two fields
        -BrochureName
        -Active (y/n)

tblStaff has two fields
        -StaffName
        -Active (y/n)

tblOrganization has two fields
        -OrgName
        -textbox (0 and 1, explained below)

tblResourceDistribution has 5 fields
        -DistribDate
        -StaffName (lookup from tblStaff ->Staffname
        -Organization (lookup from tblOrganization ->OrgName)
        -Brochure (lookup from tblBrochure ->BrochureName)
        -Quantity

The form has a combo box for StaffName and Brochure.  It has a textbox for 
the date and quantity.  Then I have two list boxes associated with the 
organization name.  the first list box is a listing of all of the entries in 
tblOrganization with a 0 and the second listbox is a listing of the entries 
in tblOrganization with a 1.  These two listboxes would move the name of the 
organization from one to the other using an update query.

So, all of this lets me ask - Is it possible to distribute 25 brochures each 
to 4 organizations (total 100) and when entering the distribution, move the 
4 organizations out of the first listbox and into the second listbox using 
my update query and then click "submit" and have it update 
tbResourceDistribution in such a way to accomlish two tasks; (1) run a 
report that shows the total distribution and (2) run a report that shows how 
many brochures each organization received.  This is one portion of the 
database I'm working and the goal is to make the entering of similar data a 
thing of the past - one entry is better than 4 in the eyes of my coworkers.

Is this possible?  I created an update query thinking it would move the data 
from the form fields into tblResourceDistribution, but it doesn't seem to 
move the data over.  I also changed the update query to a new table query to 
see if it was even working correctly.  It creates the temp table with the 
fields, but the fields are empty even though I identify the appropriate 
fields from my form in the query.  BTW - I'm using Access 2007. 

0
Rob
1/18/2010 7:11:25 AM
access 16762 articles. 3 followers. Follow

1 Replies
2295 Views

Similar Articles

[PageSpeed] 1

Your design looks good, so you should be able to Execute an Append query to 
put the data into the junction table.

To get you started, this is how you would automatically add all brochures 
for all organizations:
1. Create a query using tblBrochure and tblOrganization. There will be no 
line joining the tables in the upper pane of table design, so the query 
outputs every combination of brochure and organization.

2. In the Criteria row under the OrgName and BrochureName fields, enter any 
criteria you want to limit the organization or brochure (e.g. to exclude any 
that have already been assigned.)

3. Change it into an Append query (on the ribbon.) When Access will ask what 
table to append to, its tblResourceDistribution. You see a new Append row in 
the query design grid.

4. In the Append row, choose the target field names. For example, put 
Organization under OrgName, and Brochure under BrochureName.

5. In a fresh column of the Field row, enter the date, e.g.:
    #1/20/2010#
In the Append row under this field, choose DistribDate.

6. Repeat step 5 for the Quantity you want to assign.

At this stage, if you run the query, you would append the records to the 
target table. If you want to do this programmatically, you need the query 
statement you just created to use in your code, so switch the query to SQL 
View (on the ribbon), copy, and paste into your code. The code would look 
like this:

Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "INSERT ...
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) appended."
Set db = Nothing
Forms![YourForm].Requery        'so it sees the new records

This may help you get the SQL statement into your code:
    Copy SQL statement from query to VBA
at:
    http://allenbrowne.com/ser-71.html


-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Rob S" <sanfordlaw@inebraska.com> wrote in message 
news:##qv51AmKHA.3792@TK2MSFTNGP02.phx.gbl...
> I'm still learning Access, so I must apologize in advance for my question. 
> I have four tables, tblBrochure, tblStaff, tblOrganization, 
> tblResourceDistribution.
>
> tblBrochure has two fields
>        -BrochureName
>        -Active (y/n)
>
> tblStaff has two fields
>        -StaffName
>        -Active (y/n)
>
> tblOrganization has two fields
>        -OrgName
>        -textbox (0 and 1, explained below)
>
> tblResourceDistribution has 5 fields
>        -DistribDate
>        -StaffName (lookup from tblStaff ->Staffname
>        -Organization (lookup from tblOrganization ->OrgName)
>        -Brochure (lookup from tblBrochure ->BrochureName)
>        -Quantity
>
> The form has a combo box for StaffName and Brochure.  It has a textbox for 
> the date and quantity.  Then I have two list boxes associated with the 
> organization name.  the first list box is a listing of all of the entries 
> in tblOrganization with a 0 and the second listbox is a listing of the 
> entries in tblOrganization with a 1.  These two listboxes would move the 
> name of the organization from one to the other using an update query.
>
> So, all of this lets me ask - Is it possible to distribute 25 brochures 
> each to 4 organizations (total 100) and when entering the distribution, 
> move the 4 organizations out of the first listbox and into the second 
> listbox using my update query and then click "submit" and have it update 
> tbResourceDistribution in such a way to accomlish two tasks; (1) run a 
> report that shows the total distribution and (2) run a report that shows 
> how many brochures each organization received.  This is one portion of the 
> database I'm working and the goal is to make the entering of similar data 
> a thing of the past - one entry is better than 4 in the eyes of my 
> coworkers.
>
> Is this possible?  I created an update query thinking it would move the 
> data from the form fields into tblResourceDistribution, but it doesn't 
> seem to move the data over.  I also changed the update query to a new 
> table query to see if it was even working correctly.  It creates the temp 
> table with the fields, but the fields are empty even though I identify the 
> appropriate fields from my form in the query.  BTW - I'm using Access 
> 2007. 

0
Allen
1/18/2010 12:02:10 PM
Reply:

Similar Artilces:

Field type question
Can you use a check box instead of a radio button? ...

change color of bar on multi-day event shape
Is there a way to change the color of the "bar" across the top of a multi-day event shape? I can change the fill color, but not the bar is always blue. url:http://www.ureader.com/gp/1127-1.aspx On Tue, 16 Mar 2010 22:43:11 +0800, "Jennifer Virley"<virleyj0@hotmail.com> wrote: >Is there a way to change the color of the "bar" across the top of a >multi-day event shape? >I can change the fill color, but not the bar is always blue. Open the Drawing Explorer window (menu View -> Drawing Explorer Look for the appropriate shape on the ...

How do I sort by calculated field
relationship = one buyer to many purchases. Report is summary of buyer purchases. Number of purchases and total purchased. The number of purchases is calculated "=Count([Lot Info]![Lot No])" , the total purchase is calculated "=Sum([Price])" How can I make the report sort by descending total purchases? By descending total lots? To get the information to report one total line sorting and grouping is set to group by buyer number, group ascending. group on each value, interval =1, keep together = whole group. Presumably the sum of price is in a group footer, so...

Earned Value Report in Project 2007
I'm using MS Project 2007, have input all my tasks, costs, set baseline so all my numbers are populating great. I'm using the visual report to generate the earned value over time report (the pivot table and chart). Unfortunately while I am really good at most things Excel, pivot tables is not one of them. Project is populating the table with Quarters 1, 2 and 3. I only need Quarter 1 for my report. Is there a way to hide or delete the other two quarters from the table? Thanks for the help! Teri Hi Teri, I am guessing that you have your "Time Weekly Calendar&q...

Can I exclude the criteria Value from the query results?
I am using the [Dupe_Joiner_Title_ID] to match a list of records from a duplicates table against an ID form field, but want to exclude the actual value that the query uses as the criteria from being seen in the continous form that is linked to the query, but shows all other matching records for the form's current record. This is the criteria code: [Forms]![frm_Runs]![frm_Street_Joiner_Main].[Form]![frm_Street_Joiner_Sub].[Form]![Joiner_Title_ID] that is in the 'Dupe_Joiner_Title_ID: Joiner_Title_ID' column. This is my SQL: SELECT Tbl_Street_Joiner_Dupes.Address, Tbl_Stree...

Changing line appearance for future values
Using Excel 2007. Is it possible to change the appearance of a line in a chart for values in the future (like projections)? I would like to go from solid to dashed and keep the same color and weight. Either you format line pieces individually (manual work). Either you use 2 series to plot in the chart. Use IF functions to see on which series a given value should be shown. Use NA() for values that should not be plotted. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "RhysPieces" wrote: > Using Excel 2007. Is it possible to change the appearance of a line...

Conditional formating based on another cell value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've managed to use the solution described below, but I haven't been able to figure out how to use this for formating a cell based on a date entered into another cell. <br><br>Example: When 1/15/10 is entered into AG4, P4 is highlighted green. <br><br>Any help would be appreciated. <br> ----- <br> &quot;On 6/30/08 7:54 PM, in article 59b52d02.-1@webcrossing.caR9absDaxw, <br> &quot;Simon@officeformac.com&quot; wrote:http://www.officeformac.com/macimages/btn_publish.gif &l...

Listing all Fields
OK here is the problem. I have a table that has Key Table: KeyID KeyDescription Key Location Personnel Table: KeyID LastName FirstName I am trying to create a report that lists all keys and under that any personnel that have the key. Now I tried to create a simple report but every time I try to list "all" the keys, it only list them if there is are personnel related to that key. I want all keys listed even if there are no personnel connected to that key. HELP! BTW, I am a newbie and using the wizards are my main way of creating things. I can drag and drop controls on to fo...

CSS and disabled fields
Is there something special I have to do to Stylize a disabled field? It appears to me that this is a bug... but it could have been blocked on purpose for a reason. Is your CSS valid? Submit your question with a link to a publicly accessible page that exemplifies the issue at IE Web Development Forum http://social.msdn.microsoft.com/Forums/en-US/iewebdevelopment/threads Regards. "Rob" <Rob@discussions.microsoft.com> wrote in message news:EA4C6253-1447-45A5-91D7-1BA5FF140BD7@microsoft.com... > Is there something special I have to do to Stylize a disa...

Function to format a value into percent with no decimal
I have about 50 rows of data, each with 2 columns. The first column (A) holds a number; the format is General. The second column (B) divides the number in A over 51, using this formula =A1/51 (and =A2/51, =A3/51, etc.). The format for column B is Percentage with 0 decimal places, so the results look like 25%, 4%, etc. I'd like to create a third column (C) which concatenates the results of columns A & B to yield something like: 13 (25%) But instead I get: 13 (0.254901960784314) The function I'm using is: =B1 & " (" & C1 & ")" I'm thinking I...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

DIsplaying negative time values
Hi, I have two columns of time values. I need to determine the difference between the two values and return either a positive or negative time value. The table below is in hours and minutes Time A Time B 09:00:00 09:00:00 09:00:00 09:03:00 09:00:00 08:58:00 In the above example First row should be "00:00:00", Second row should be "00:03:00", and Third row should be "-00:02:00" When subtracting Time B from Time A for the first two rows i get the correct value, but the third row returns as "##########...

HR: Not all required fields have been entered
I'm attempting to enter a new employee, it looks like I have all required fields entered, but I'm still getting this error message. Anything else for me to look for or pay attention to? THanks! PS: I've entered many new employees in, what I think is exactly the same way, and never had this error stop me. ...

Query to find the sum of fields...
Hello all,I have an orders table that stores all the orders from variouscustomers sorted by order date descending (I guess that part doesn'treally make a difference). Anyhow, I want a query that gives me a"grand total" of every order from a particular customer. I'm reallynot sure how to go about it... with a Select Query or CrossTab Queryor even a different way completely.What I have is a table called [ORDERS] with the following fields:OrderIDOrderDateCustomerIDOrderTotaletc.Obviously there are orders placed from the same customer throughoutthe year but would have different...

How do you add a drop down calendar to a form field?
Is this possible? Do you mean the "Class" MSCAL.Calendar.7...the Calendar Control? "kjv77" wrote: > Is this possible? What version of Access are you using? I believe that this is built into Access 2007. HTH "kjv77" wrote: > Is this possible? It is. Date Picker in the Form Properties Format Tab "roccogrand" wrote: > What version of Access are you using? I believe that this is built into > Access 2007. > > HTH > > "kjv77" wrote: > > > Is this possible? There's VB, C, and J code to do this...

Styles and Set Numbering Value problem
Running MS Office Pro 2007 with Windows XP Pro. I have styles defined for numbered lists. These styles have simple characteristics, like indent, space before, etc. - nothing special. I want the numbered lists to appear throughout the document, with each starting at the number 1. When I try to start a new list using the Set Numbering Value dialog, I get a message saying "Changing the number format for this list updates the style "NumList" and reapplies the style to each paragraph. Do you want to continue?" If I select no, the starting number is not chan...

How to suppress zero value check checks and remittance
Any one knows how to avoid printing the zero value remitances during the checks run in PM. GP7.5 Thanks in Advance. ...

Trying to select a specific range based on the time value of user form input
Here is the cmdOK_Click event of my user form. What I need it to do is based on the time input in the pckApptTime on my form to have it offset to a different range of cells (ie: if time is 7:15, then the active cell offset is range A1, looping for a blank cell. If the time is 8:30 am then the range begins at a10, 9:45 range a20, 1:15 range a30, 2:30 range a40, and 3:45 range a50) Where in the Sub do I put this information? and what do I put to have the OK_Click do this automatically? Private Sub cmdOK_Click() Range("A1").Select ActiveWorkbook.Save (this is to update the ...

Multi-Language Support
I made some resource DLL to support different languages. m_hInst = AfxLoadLibrary( _T("lang.dll") ); AfxSetResourceHandle( m_hInst ); However, when I try to change the language from one to another, tooltips and messageboxes are changed but menu is not. Is there any way to completely change every resources? ...

Transferring option group value
I'm trying to create a form for adding new record only. How to do that ?. In that from,I want to put an option button ( group ) to shown or hide some text box ( control ) in my forms.But I also want to transferr the option group value to a field once the form close or the Save_cmd is clicked. Then in another qury I need to join the value transferred from the option group with another table so that the data appear as a text in another table. Please advice how to do that since I have no idea with coding? shiro wrote: >I'm trying to create a form for adding new record only. >...

Maximum value
Hello, I have a table in the following format: ID Diagnosis Fraction 1 X48 0.01 1 Y52 0.5 2 U78 0.85 etc What I need is only those records that have the highest fraction for each ID. ID Diagnosis Fraction 1 Y52 0.5 2 U78 0.85 etc I have used the Max function in my query. However, that does not work because the information is also grouped by diagnosis as well as ID and this causes all of the lines to appear. Does anyone know how to get around this problem? I can't just bring through Fraction ...

Copy formulas, format, validation to sheet with values
Environment: Excel 2000, Win 2000 I have a workbook with 12 sheets, one for every month of a year, all the same size, same format, just different values. I am still making changes to the workbook application, e.g. formulas, formats, validation, conditional formatting. So I change the sheet for one month, and then have to get the changes duplicated for all the other 11 sheets in the workbook. What is the best way to do that, especially for the formulas? Paste Special with deleting the constants afterwards is not a solution here, as some of the target sheets already have values entered manually...

sum a cell value to a range
Hi I need to sum value in A4 to a range B4:B12 every time I enter a new value in A4. How can I do it? Thanks in advance Osmario "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: > I need to sum value in A4 to a range B4:B12 every time I > enter a new value in A4. How can I do it? =SUM(A4,B4:B12) I wrote: > "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: >> I need to sum value in A4 to a range B4:B12 every time I >> enter a new value in A4. How can I do it? > > =SUM(A4,B4:B12) Or did you mean that you want to a...

sum letters with values and numbers
I have a time sheet that where I want to do various totals. When it's hours worked, no problem. This department also uses letters (P, V, H) to indicate time off with pay (personal, vacation, holiday). They would also like to use the lower case version to indicate half a day. How do a get a sum or sumif function to see the letters as their numeric equivalent and total them with the other numbers entered? I know this is probably overly simple for this site, but I can't find it in the Help section or I can't phrase the question will enough for Help to find my answ...

Newbie needs a little help on the value axis
Never created a chart from Excel before. My chart is simple. I simply need to chart 10 columns. 1 for each year going back. Actually each year needs to show 2 columns side by side, 1 showing the count of our total portfolio (avg 180,000), and the other showing our onboard staff for the given year (avg 50). As you can see already, the problem I'm having with the chart I created using the wizard, using column type, is that the value axis goes clear up to 200,000 in 20,000 increments. So his column shows nice and fine but the staff on board column is not even visible as it doe...