Number of Feilds in a query

[chr1stoper1]

Hi Guys



I have a question regarding the number of fields in a query. I have a query that is looking at production scrap by categories. There are fifteen categories of type of scrap and in addition to these I am using the query to work out the total % of total scrap for each category .My problem is there are not enough fields to do all the calculations .Is there a way of adding more or is there a better way of doing what im trying to achieve?

Thanks in advance for any help

[ranman256]

I think you are doing it wrong, you don't add fields, you add records.
you need a tCata table.
in it is 1 field,[cata] with all the various records a category can be.

the table tScrap would have the master item, and table tScapCata is a child table of tScrap. (Lookup child records)
this way 1 SCRAP item can have infinite categories.

tScrap table
[scrapID]
[item name]
[date]

tScrapCata table
[scrapID]
[cata]

this would allow you to count and calculate percents.

[chr1stoper1]

I think you are doing it wrong, you don't add fields, you add records.
you need a tCata table.
in it is 1 field,[cata] with all the various records a category can be.

the table tScrap would have the master item, and table tScapCata is a child table of tScrap. (Lookup child records)
this way 1 SCRAP item can have infinite categories.

tScrap table
[scrapID]
[item name]
[date]

tScrapCata table
[scrapID]
[cata]

this would allow you to count and calculate percents.

Hi thanks for the reply. My table is populated with product name ,product number, the machine it was made in, amount produced, amount scrap then fifteen different reasons for scrapping and the number of that part that was scrapped that day for each of the fifteen reasons . This is how I receive the data each day. I have just been populating a table

[June7]

There is a limit of 255 fields in table or query. Period.

You might need a UNION query here to rearrange the data into a normalized structure. Then use that query as the source for subsequent queries to do the aggregate calcs.

SELECT ID, ProductName, ProductNumber, MachineID, AmountProduced, AmountScrap, "R1" AS ReasonCode, Reason1 AS ReasonData, Part1 AS PartNo FROM Tablename
UN ION SELECT ID, ProductName, ProductNumber, MachineID, AmountProduced, AmountScrap, "R2", Reason2, Part2 FROM Tablename
...;

That essentially emulates a dataset that would result from a query joining 2 normalized tables as described by ranman.

[chr1stoper1]

In a query in design view can you have more than 27 different selections?

[orange]

Before deciding HOW to do your calculation, get your facts clearly stated. Focus on WHAT data/outputs are important. And then design the algorithms/calculations to get the correct outputs. Then adjust to get the format you need.

Because you receive data in one format does not mean that is how it should be stored. Database tables are built on some underlying concepts (basically Normalization) that facilitates manipulation.

Show us

• some representative inputs you receive
• a sample calculation
• some sample output you need

Good luck

[June7]

Limit of 50 SELECT lines in a UNION query.

[chr1stoper1]

Hi guys
the attached files show the main menu of my database all of which now work ok. Also there are two reports which populate my tables daily after ive added the dates and filled in the blanks on the detail numbers. The query is now my main problem. If I isolate one type of scrap (restart) for example/in the query I want to add an expression to work out the % of the scrap that was made up of restart. Then just return the top 10. I keep getting a overflow fault.
Any ideas?

[June7]

The only file in the zip is an Excel. What should be done with it?

Recommend no spaces or special characters/punctuation (underscore is exception) in naming convention. Instead of %Short, use PctShort. But why are you dividing by 100 if you want to show percent?

[chr1stoper1]

Hi
Thanks for the reply. The excel sheet is how I receive the scrap info for the previous day. I run a couple of macros to add date and fill in the gaps which are in the sheet if one machine makes a left and right part the machine number appears only once. I upload the whole finished sheet to a table and I have set up the database to analyse it in lots of ways. Everything works great but now I'm trying to ask the database "what is the worst 10 jobs by percentage of total scrap made for each different category" the way I work out percentage total amount of scrap by category divided by one percent of total scrap which is (total scrap divided by 100).
It is just the way i've always done it.
Would this have an impact on why I'm getting an overflow error?

[June7]

Probably. Access has limit on size of numbers it can deal with. Review http://answers.microsoft.com/en-us/o...f-15112836c77a