Insert select query result into a table?

Posted on 2008-10-30
Last Modified: 2011-10-19
I'm making a form for speeding up the process of creating commercial invoices.  I attached screenshots for visualization.

The invoice entry form has an underlying table and each line item has it's own field.  (IE: Line Item 1, Line Item 2, etc).  My goal is that when you click the pencil button next to the corresponding line item, it will pull up another form (which is based on a select query) that allows the user to enter a SKU number and the description of the product is pre-populated.  From there I want the user to be able to click "Add Line Item" to add the product details into the appropriate field (ie: Line Item 1)

How can I achieve this?

I barely know anything about coding so please be as descriptive and simple as possible.

Question by:paintb4707
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 44

Expert Comment

ID: 22840792
It would serve you well to examine the Invoice forms associated with the sample mdb that came with you copy of Access as well as the associated tables.  The mdb is named Northwind.mdb and is normally located in:

C:\Program Files\Microsoft Office\Office\Samples\

for Access 2000.  For Access 2003 swap Office for Office11


Author Comment

ID: 22840847
Just to further explain my question, I basically want to know how to code the "Add Line Item" button so that it adds the description of the product into it's appropriate field in the invoice table.  The select query already works as intended and the user is able to input a SKU to populate the Description field but I don't know how to take this result and insert it into a table.

Author Comment

ID: 22841291
I looked at the Northwind database.  I think it's a nice idea using a drop-down but I feel that it would be too time consuming.  There are THOUSANDS of products that we manufacture and it would just be much faster if you could enter the SKU number instead.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 44

Expert Comment

ID: 22842957
You do have a table tblInvoices and another table tblLineItems.  Each record in tblLineItems carries a foreign key from tblInvoices - correct?  That way you could have an invoice with one line item or a hundred - it would not matter.  Are we on the same page?   If not, I would recommend you have another look at Northwind.

As to the reason for the dropdown, you could limit the SKU's to the dropdown, and virtually eliminate typo's.  With auto complete on, you would start to type the name, and very shortly you would narrow it down to a few from which you would click on the correct choice.  A SKU should never be typed except when entering new items.  Just my 5 ¢.

Author Comment

ID: 22843741
I'm only a beginner in Access, picking it up as I go along so I don't really follow what you're saying.  I'm looking at the Northwind database and I don't really see how the line items correspond to each invoice record.  I don't really understand how you can create an invoice and say these items will be listed for this record and another set of items will be listed for the next record.

I'm not trying to make some huge complicated database.  I think it would just be easier for me if I could accomplish my original goal, by taking the results from the select query and importing it into a line item field from the invoice table.
LVL 44

Accepted Solution

GRayL earned 350 total points
ID: 22844109
There is one Invoice record and in another table, InvoiceDetails, there may be several LineItem records, one for each line item.

InvoiceNo - primary key, may be autonumber
CustomerID - foreign key from Customers table
InvoiceDate - date time

DetailID - autonumber
InvoiceNo - foreign key from Invoices - this is where the invoice number gets repeated

In Northwind, the two tables are Orders and OrderDetails.  For some strange reason there is no DetailID field.  If you click on the Order field in OrderDetails, and sort it ascending, you will see several records containing the same order number.  Within each group, the order is the Item Number.  You do not need to store that number,  your form/report can be coded to generate it automatically.  If you open the form Orders and print the first record, you will see the three items in that order.  In this case you see the ProductID.    Some companies insist on seeing something called an Item Number, and if that is your case, sobeit.  Do an Inner Join on the invoice number in these two tables and immediately you have all the data needed for your invoice.

This is all part of what is know as a One-Many relationship.  This is fundamental to creating an Invoice (0ne) with several line items (Many).  As a beginner, you would do your self a service to spend several hours/days poring over the various features of Northwind.  It is a sound design, has been with the Access family of releases since virtually day one with very little change.  

Author Comment

ID: 22853715
Thank you.  I figured out how that works and made a separate table for the line items then insert a subform for it.

I have two questions now.

One being... (as of right now) I have tblProducts with SKU and Description fields.  Now I made the Description field of tblLineItems to be a lookup of tblProducts.  SKU being the first column and Description being the second.  I made it so that it stores the Description (column 2) but when I actually go in and choose a product it inserts the SKU and not the Description like it's supposed to.  Why is this?

My second question is... I'm waiting for a more complete list of products to add to tblProducts.  The complete list will include Total KG and Price per 1000.  How can I make it so that when you choose a product in the Description field it populates the appropriate KG and Price?

Thanks in advance
LVL 44

Expert Comment

ID: 22882581
Why would you want to store the description field in tblLineItems when it is already in tblProducts?  A no-no in db design.

I think you need another table tblProductsPkgs in which your products are broken down into packages.  Here is where you need the SKU, PgkID, TotKg, and PricePerK

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question