New user question

[eTom]

Well it's coming along great. I have changed a few of the ways things are stored in my main tables now... I had been having it store Country name in my main Incident table as an example. I'm not sure why. Everything is all ID now and related properly, I think.

I have a question about queries. I've created a query that takes my Products table and filters out everything that has a check mark in "Obsolete." It's a field I created so that I could eliminate products no longer being made from the combo box in my entry forms. (We update products a lot so the list will get pretty long really quickly.)

Do queries update themselves? If I have the form base the combo box data on the query, will it re-populate the query every time I load the form to include any new items that I've added to the Products table recently, or do I have to include some function or program call to have it update itself?

Thanks again!!

 

[Scooterbug]

Do queries update themselves? If I have the form base the combo box data on the query, will it re-populate the query every time I load the form to include any new items that I've added to the Products table recently, or do I have to include some function or program call to have it update itself?
Thanks again!!

When a form loads, the record source(s) for the form, list boxes, combo boxes and subforms are loaded up. If you make a change to the underlining query, then yes, it will reflect those changes. So if you add products to your table or make them as Obsolete, then it will be reflected in the combo box everytime you load up the form. You can also force the list to requery via code. For example: Me.ComboBoxName.requery.

 

[eTom]

Oh nice! Good to know. I'm excited to maybe get to a point where I get to write some actual code.

I went into this thinking there'd be some fairly heavy coding involved, but so far it's all so graphical... I suppose that's a good thing!

Now that I've got this form nailed down, I'm able to enter this stack of Incidents that involve multiple Products all in one. Once that's all said and done, it's time for me to start working on building data reports for the bosses.

I imagine that's when I start poking around "reports," correct?

I assume they're going to want to see things like the top three most complained products, broken down by category or country. I'm pretty excited!

 

[Scooterbug]

Yup, once you got the data settled, then it's on to reports. They pretty much have the same concept as forms (Forms and Subforms = Reports and Subreports).

 

[eTom]

Great!

One more little issue I've had since setting up this forms. I think it might just be the way it works, but I figured it can't hurt to ask.

When I drop down the combo box for Products, it lists my products based on five fields. ProductID, which is our in-house item number, and which I have set to a width of 0. This is the actual value passed on to the Incident table of course. It's the primary key in Products table, so it keeps everything related properly. (As far as I've seen so far anyway!

The other four fields are basically the ways we discern our products. Brand, specific SKU, customer demographic and product size. I've adjusted the width of each column in the combo box properties to show everything roughly the way I want it, and it is easy to use. You pick from an easy to read list, and the form stores the ProductID. Everyone's happy.

Unfortunately, as soon as you click on the product and move on to something else in the form, the only information shown in the combo box as "chosen" is the first visible column: "Brand." Since I just clicked "Brand A, Product 123, Demographic X, 2 kilogram," I know that I've chosen the right product, but it only shows as "Brand A" when I've moved on to other things. Is there anyway to have it display the full range of columns after I've moved on, or is this just the way of the world?

Thanks! I hope it made sense, it's an... odd issue.

 

[Scooterbug]

Not really an odd issue...the combo box will only display the first column when a product is selected (as far as I know). The other columns are still available, just not visible. What you can do is to have a couple of unbound text boxes, and with a bit of code you can display the other data. All you need to do is to reference them. For example you could up this on the After Update Event of the combo box:

Me.txtSpecificSku = me.ComboBoxName.Column(2)
Me.txtCustDemograph = me.ComboBoxName.Column(3)
Me.txtProdSize = me.ComboBoxName.Column(4)

The data in each column is still there, you just need to tell Access to put the data into the unbound fields. Remember, when referring to columns in combo boxes/list boxes, the number starts at 0. So the first column is 0, second is 1, etc.

 

[eTom]

The thought of getting to code something is very tempting. Unfortunately as it's the combo box that I'm going to be using five to ten times per form now, it won't make for a very pretty form. Maybe I'll just un-hide the item number / ProductID and have it use that. At least then it's specific. ^^

Not really an odd issue...the combo box will only display the first column when a product is selected (as far as I know). The other columns are still available, just not visible. What you can do is to have a couple of unbound text boxes, and with a bit of code you can display the other data. All you need to do is to reference them. For example you could up this on the After Update Event of the combo box:

Me.txtSpecificSku = me.ComboBoxName.Column(2)
Me.txtCustDemograph = me.ComboBoxName.Column(3)
Me.txtProdSize = me.ComboBoxName.Column(4)

The data in each column is still there, you just need to tell Access to put the data into the unbound fields. Remember, when referring to columns in combo boxes/list boxes, the number starts at 0. So the first column is 0, second is 1, etc.

 

[Scooterbug]

Curious as to why you would use the same combo box 5-10 times per form.
Other option is to put in a small list box, set the row source equal to the products table and include all the fields you want to display. Then, in the criteria for the list box, have the productID = me.comboboxname. Then just do a me.listbox.requery on the after update event of the combo box.

 

[eTom]

I use the same combo box because it's part of the sub-form / sub-table that allows me to enter multiple products for each Incident (main table) record. After reading your reply I realized that if these text boxes were part of the subform, as I create a second record it would add a second set of text boxes. Duh.

It's Monday, I'm a little slow this morning!

 

[Scooterbug]

You can do it two ways.

1. Put a combo box on the main form and any other txt boxes you might need (Quantity, etc). Have a command button that fills in the necessary fields on the subform. For example:

[Forms]![frmOrders].[LineItemSub]!SKU_ID = Me!CBO_PICK_LIST.Column(1)
[Forms]![frmOrders].[LineItemSub]!PRODUCT = Me!CBO_PICK_LIST.Column(2)
[Forms]![frmOrders].[LineItemSub]!PRICE = Me!CBO_PICK_LIST.Column(3)
[Forms]![frmOrders].[LineItemSub]!PACKAGE_ID = Me!ORDER_ID
[Forms]![frmOrders].[LineItemSub]!ORDER_DATE = Me!ORDER_DATE
[Forms]![frmOrders].[LineItemSub]!CUSTOMER_ID = Me!CUSTOMER_ID

You could also do the same thing by putting the combo box in the form header of the subform.

2. You could build an Insert Sql statement in code.

Both ways give you some latitude with data validation. Just make sure that you requery the subform when all is said and done so that it shows up after you add it.

 

[eTom]

My next question is about reports, and although I realize that means it should go into the Reports section of the forum, you guys are already somewhat familiar with my layout and level of skill, so I'm just going to reply here rather than start another thread.

I've built my first report, listing Incidents grouped by product. Seems all fine and dandy, except where it lists "Category" it's listing the actual CategoryID rather than the second field that is the actual description of the category. I am unsure where in the properties to look to adjust this.

 

[Scooterbug]

In the query that is the source for the report, add the table that has the information about the catagories (I will assume it's tblCatagories). Add that table to the query, and link the two CategoryID fields together. You can then add the CatagoryDesc from the Catagory table to the fields. Save the query, then click on the available fields in the report design view. You should now see a field like this: tblCatagory.CatagoryDesc
Add that field to the report and it will display the Catagory Description.

 

[eTom]

I've been using a table as the source for the report. My IncidentDetails table, to be specific, since it has the involved ProductID, Quantity, and CategoryID.

They're probably the main three items I'll need displayed.

Is it customary to build a query first to use as the basis for a report?

 

[Scooterbug]

I've been using a table as the source for the report. My IncidentDetails table, to be specific, since it has the involved ProductID, Quantity, and CategoryID.

They're probably the main three items I'll need displayed.

Is it customary to build a query first to use as the basis for a report?

Generally it's better to use queries, they are much more flexible as record sources than tables. With the use of Primary/Foreign Keys (Like you have now) is one big reason for doing so. You also can apply filters (via criteria) when using queries.

 

[eTom]

If I click on the ... button in source in the form's properties, I get a window similar to the query builder. Is that what you meant? I can add the table there, and the existing relationships come up.

Oh, and then I can add the field. Awesome!

 

[eTom]

Wow. After creating a query, I can see the possibilities and power behind reports. And even when I had forgotten a field I needed, I just went back and added it to the query and then to the report. Easy peasy!

Now I have another question, of course. I can export the report to Excel, but of course it's a lot more data than I need and it doesn't graph well. I've essentially designed the report to list and total up Incidents by each specific product.

Is there a way I can have the Sum from each product listed at the bottom of the report? A sort of summary, if you will. That way when I export, I can graph just the summary at the bottom and it will be a nice pretty bar graph with a few dozen bars. I could take the totals manually, and will if I have to, but part of the idea behind a database was automating this kind of thing as much as possible.

 

[Scooterbug]

Access has some graphing capabilities...I've never had need for it so i'm unfamiliar with it.

However, if you want to export sums into Excel, the best bet would be to create a new query with the totals displayed in them. To have the query sum up, click on the Sigma icon (Looks like an Uppercase E) in the Query Design window. From there, you can sum up a field, average and a host of other things. You can then either manually export the query into excel, or you can use VBA to do it automatically by using the

docmd.transferspreadsheet

command.

 

[eTom]

However, if you want to export sums into Excel, the best bet would be to create a new query with the totals displayed in them. To have the query sum up, click on the Sigma icon (Looks like an Uppercase E) in the Query Design window. From there, you can sum up a field, average and a host of other things. You can then either manually export the query into excel, or you can use VBA to do it automatically by using the

That's what I've been trying, but I'm unsure how to get group totals. So far I've only been able to add a single Total row to the bottom of the query. Ideally I'd like to total it up by individual product, or at least brand. Would I need to create a query for each Product in order to get a total?

 

[Scooterbug]

The sql for a query would look something like this:

SELECT sum(quantity) as SumOfQuantity, ProductID
FROM IncidentDetails
GROUP BY ProductID

That should sum up the quantity of each unique ProductID in your incident table. You can then use that query along with the table that has your product names (Joined by the ProductID) to show the actual product name if you wish.

 

[eTom]

The sql for a query would look something like this:

SELECT sum(quantity) as SumOfQuantity, ProductID
FROM IncidentDetails
GROUP BY ProductID

That should sum up the quantity of each unique ProductID in your incident table. You can then use that query along with the table that has your product names (Joined by the ProductID) to show the actual product name if you wish.

If it were only that easy. I copied and pasted that, and looked up the sum() expression a little bit to try to understand what it was doing, and it doesn't want to work for me.

I create a new blank query, switch to SQL view and paste that in. I switch to DataSheet view and it asks for an item number in a dialog box. I type one of ours in manually to test it out, and it gives me a SumOfQuantity of 2656.

Totalling up the "Quantity" field from the "IncidentDetails" table manually I get 28.

I'm unsure as to why.

Edit: Never mind. I'm... slow. I use "ItemNumberID" instead of ProductID. I switched our in-house ItemNumber for each product to the primary key, since it's always going to be unique to that product. (And a lot of the data entry will be based on that number; should make it easier for some of the accounting people)