adding a sum query to a form

[smeghead67]

Hi, This is my first post here.



I am working on a inventory db. I'm creating a form using the wizard to see if it will work, then I will go back and use the regular design tools to make it look nice.

I'm accessing two tables Parts and partstore.
in Parts my fields are ID (which my users will not see) pn (The part number they will refer to it by) & description.
in partstore the fields are ID (Again the users do not see this) pid (they wont see this because it refers to Part.ID) binid (Which is the storage location) and qty (the amount in that bin)

I created a query that pulls the info from parts now i can create a form and sub form using this data. I created the binid field because we may have an item in more than one bin and we have 4 unique storage areas. the info that goes into binid starts with a letter L for lodge B for the repair bench and so on. I created 4 query's that get the sum of the part number that has a certain letter. Here is the code from one of the querys

SELECT Sum(partstore.qty) AS Lodge
FROM partstore
WHERE (((partstore.binid) Like "L*")) And Parts.id;

When I try to add this to the form, it give me an error about i cant use info from a table and a query that refers to it ?!? is this sum query wrong or what do I need to do to put the four total on this form?

Thank you
Smeghead67

[June7]

What is Parts.id? If Parts is a table it is not included in the SELECT clause. The WHERE clause is just confusing the SQL engine.

[smeghead67]

OK, I simplified my query to

SELECT DISTINCTROW Sum([partstore Query].[qty]) AS Lodge
FROM [partstore Query]
WHERE (((partstore.binid) Like "L*"));

This works when i run it to test it.

I am creating a form with a sub form from two querys. Parts and partstore if I do that it works.

I want to add a query to that form. The text of the query is listed as code above. When i try to add it in form wizard, i get the following error: "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

All the data I'm using is from querys. What concept am i not grasping?

[alansidman]

I am not sure I understand the whole thing but try putting the results of your query in the footer of your form. I think because you already have a record source for both the main form and the sub form, that Access is getting confused.

[smeghead67]

Alan,

It's part of my attempt to make my job easier. I work in a casino, this DB in its final version will keep track of parts, slot machines and how many parts are being used in any machine. I'm trying to rush the inventory part because the end of the quarter is coming up and i will have a hard count of all parts. it takes two people one week to accomplish that count. so you understand why i would want to make my life easier.

this form is for the parts table. I get the info from a query it will bring up the part number we refer to the part by, a description, a value of an individual part, a reorder level, a repairable yes/no, capital yes/no, a ledger# (a look up to another table), the slot manufacturer (a look up to another table), and the parttype (a look up to another table). to this i have another table partstore i get its info from a query and want it as a sub form to the parts in it is my binid (Text), partid that refers to the key on the part table and a quantity. As i can have parts in more than one bin in any of my storage areas, i want a total of how many parts i have in any area. I want to put the totals onto the main part of the form.

Does that help?

[June7]

Not really. If you want to provide project for analysis, follow instructions at bottom of my post.

I suspect that all those lookups could be handled by building a query that joins tables.

Also possible that instead of form, would be better to use a report for this output.

[smeghead67]

Sure,

here it is.Slot.zip

[June7]

First, the Parts main form has RecordSource of the Parts table, not a query.
Second, the partstore subform has a RecordSource that is a query but that query does not include the primary key ID field. This query does not have any filtering or sorting or joins. Although the query works could just use the partstore table as the subform RecordSource.

i want a total of how many parts i have in any area

You want to see the total of parts in each binID? This must be on the form? Try a listbox RowSource:
SELECT partstore.binid, Sum(partstore.qty) AS SumOfqty
FROM partstore
GROUP BY partstore.binid;

Will have to use VBA code to requery the listbox after each partstore record is entered and when moving to a new parts record. This is why reports are best for output of summary data.

[smeghead67]

June,

I can change the source of the form to a query. I tried an inner join query that pulls from the tables parts and partstore, this gives me that form.

I want one field that gives me the total of all parts, your query should do that, then i want fields that tell me how many are in the lodge area, bench area, track area, inventory area, state secured area, chair storage area and barn storage area. all binid records will start with 1 letter that will designate what area it is in. the Input mask for it is, !LAA\-00\-00

I am looking though the database design forum to see if there is anything i can learn.

Ted

[June7]

Try:

SELECT Left(binid,1) As AreaID, Sum(partstore.qty) AS SumOfqty
FROM partstore
GROUP BY Left(binid,1);

[smeghead67]

June,

I tried that. it creates a result as you intended, but i can not combine it and parts query into a form or a report. I tried combining those two query's into a third one and it gave me the same message.

I tried creating a new table called testpartbin that has the area as a separate field for the area, it also refuses to be placed on a form or report.

The error i get is, "You have chosen fields from record sources which the wizard cant connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

I'm selecting fields from a query. If I cant get a sum of parts what good is a database?

I'm uploading a current version for the board to look at. Slot2.zip

Ted

[June7]

My suggestion was to use the query in a listbox or subform. I had no problem creating a listbox with that query.

Do you really need this summation on a form? Why not a report?

Why does the revised db not have forms?

[smeghead67]

June,

My suggestion was to use the query in a listbox or subform. I had no problem creating a listbox with that query.

Ok, I will look at how to do that.

Do you really need this summation on a form? Why not a report?

I can not get it to work with a report either. it gives me the same error message

"You have chosen fields from record sources which the wizard cant connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

I was used a query to pull the info from a table than tried that with this sum query to make both a form and a report but it would not allow it.

Why does the revised db not have forms?

I created a form with just the Parts Query2 but I want a sum to see if I'm below the reorder level. Trying to add that sum causes an error.

Ted

[June7]

My point about forms is they were not provided so could test with. I had to import them from the older version.

As stated, no problem putting a listbox on form. Will need code to requery the listbox after record edited/created in the subform.