Query Challenge

Apr 21, 2006

I have several Databases with similar tables with similar information, but entered differently. (Different persons made separate databases before my time) I would like to make new tables, write a query to combine the information into these tables, but I need to get the data similar before I can combine.

Example of information:

7L 24 3A719 AD one table might have it stored this way
7l243a719 AD another this way
7L24-3A719-AD or 7L24 3A719 AD both ways in this table
and so on. There are alot of combinations

I have been in attempting Mid$, Right$, Left$, Like, Not Like and others to no avail.

Looking to make all the information the same layout 7L243A719AD, then I will be able to combine information.

Any ideas / help would be appreciated.

View Replies


ADVERTISEMENT

Query Challenge

Jul 24, 2007

Hi everyone, here is one of the tougher problems ive had to deal with.

i have a number of fields, called teammember2, teammember3 etc..to teammember12. This is done because on the form, the user is able to select team members in addition to their original selection using a button.

Another field on the form is a "total days worked".

The problem now is adding the total days worked for each record based on the team member

for example:

if record 1 has : john smith and jane smith as team members
and record 2 has: john smith and tom smith as team members

i need the report (query) show the total days worked for john smith as record 1+ record 2. and for jane smith just record 1. tom smith would show total days for record 2.

im sorry for the weird explanation! i will be happy to clarify anything.

View 4 Replies View Related

Query Challenge For The Experts?

Jan 26, 2006

Create a temporary table to hold the results. Use VBA to open the data table and temporary table in two recordsets. Loop through the records in the data table and place the data in the temporary table in the required positions.

View 1 Replies View Related

Interesting Query Challenge

Aug 14, 2006

Hello all. Hope someone can help.
I want to know which of my customers received a particular service (service id: #199) during the last month. It gets complicated (I think) because of those customers that received service 199 last month, I want to know which ones did not receive any other services in the 90 days prior to receiving it. Any thoughts on how this can be done? I can find who received the service during the last month, but how do you check to make sure they have not received any others during the last 90 days?
Any help is much appreciated.
Thanks,
S.J.

View 2 Replies View Related

Challenge On Crosstab Query

Jan 18, 2008

Hope any Access guru can help me with this problem.

I want to create a cross training matrix with crosstab query in Access to show the relationship between the trainers and trainees with purpose of the trainings from the following table.

XTrainID...Purpose.............Trainer............ .Trainee
1.............Web Focus......... John................Edward
2.............Search Engine.....Mary................John
3.............CEI................... Matthew...........Edward
4............SAS................... Paul..................Mary
5.............NET.................. Greg.................Sam
6............SAS................... Jennifer..............Mary
7............SAS................... Matthew.............Edward

The resulting matrix should look something like this:

............ Greg...... Jennifer....John.............Mary........Matthew.. ....Paul
Edward..............................Web Focus...................CEI, SAS
John.............................................. .......Search Engine
Mary...................SAS........................ ....................................SAS
Sam....... .NET

I tried to use crosstab Q and picked Trainee as Row Heading, Trainer as Column Heading and Purpose as Value - with the Total option as First, but it would only show the first purpose even if trainer with the same trianee has more than 1 cross training involvement. The example is for Matthew & Edward. In this case, it only show CEI but not SAS. My challenge here is how to concatenate training purposes with the same trainer and trainee.

Any suggestion would be much appreciated.

Mike

View 4 Replies View Related

A Structured Query Language (SQL) Challenge

Jan 9, 2007

All,

I'm working on a bespoke logging feature for a web portal which records user events in a single table (tblUserLog as illustrated below).

idsession_idauth_userapp_nameeventremote_addrhttp_user_agenttimestamp
2331063924932jimApp 1Some event10.203.115.30Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2341063924934joeApp 2some other event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2351063924934joeApp 1Some event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2361063924934joeApp 1Some event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2371063924936jimApp 2some other event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2381063924936jimApp 2some other event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2391063924936jimApp 2A completely different event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06


To supplement this flat list I'm preparing a number of key stats which will be reported to administrators (e.g. unique user/session count by period, app usage by period etc).

I'm now looking to report the (10?) most active users where most active means those users having the most unique sessions. The desired results are as follows:

User Sessions
joe 23
paul 10
chris 5
jim 2
... ...

Within Access its relatively easy to create/combine a number of queries to acheive this but I need to do this via an ASP script. The challenge is to design a single SQL statement (with nested SQL?) which will return a recordset as above.

Any help on this would be much appreciated.



Thanks in advance,


Rob.

View 2 Replies View Related

A Challenge To Say The Least

Dec 5, 2005

Here is a challenge for the best of you!

I have a cattery system which will be using the following tables:

tblCAT tblVisit and so on

I need to be able to check booking availibility. NOTE: THIS IS NOT THE SAME AS A HIRE-SERVICE SYSTEM. I.E. I need to be able to check the following:

1. For every day of a cat's intended visit...is there less than 40 cats @ the cattery

FOR EXAMPLE:

At the moment I can search to see how many visits will be taking place on ONE particular day. So the challenge is:

HOW CAN I REPEAT THIS QUERY TO CHECK HOW MANY VISITS ARE TAKING PLACE ON EVERY DAY OF A CATS INTENDED VISIT.

*At the moment, my querey can only search for the total number of visits taking place on one day...I need to repeat this as mentioned.


THANKS EVERYONE :D

View 3 Replies View Related

I've Got A Challenge For You!

May 1, 2007

Here's a tricky little problem I am trying to figure out. I'm sure someone out there should be able to help me without breaking anything. Heres the problem.... I have a form that has an underlying table attached. I have a combo box pull in a pull down list of locations from that table. What I would like to happen is, I would like it so the user will select a location from that combo box, and then click a command button. When they click that button, I need whatever they chose in the comobo box to copy over to a NEW table, which will be referenced later by my program. Say the table being pulled from is called "table1", and the table I need the info to go to is "newtable". I guess it doesn't SOUND hard, but I can't seem to figure anything out on it. I'll let you experts tear it apart and tell me how dumb I am for not knowing. Be kind... I'm still a noob. :D

View 6 Replies View Related

A Challenge

May 3, 2005

Hi,

I have been thinking about this since two day and tried everything, but no luck.

I have a form with recordsource a query. A listbox contains all references and if you choose a reference, everything else on the same form gets updated. Now i want to avoid that two users edit the same record at the same time. So i thought i would make an extra field in my table with a checkbox "yes/no" wich says if the record is in use or not. Now whenever a user edits a complaint i want that when another user is trying to edit the same record a message pops up saying that the record is in use. I can get it to work, but only have the problem that when a user decides to change reference number in the listbox and call upon another record, the value needs to get to fals again on the record he was editing.

Maybe there is another way to now if a user is working on a record.

Anyone that can help with an easy solution on this?

View 1 Replies View Related

Bit Of A Challenge

Dec 21, 2005

Hi Everyone.

I am building an app which will be used to administer a small company which has several mobile service engineers.

Everything is fine. A fairly standard sort of job until the client asked for a screen showing each engineer for a given day with a bar graph showing planned start and finish times for each allocated job, together with the location! As an added challenge, I need the user to be able to click on the "Chart" and so open another screen showing full details of the job clicked on.

My first approach was to use a chart. Couldn't do it:confused:

The approach I have now used is to create a series of labels on the form "On the fly" to represent each job and the unallocated time in between jobs. I can also run a macro to display the full job details when the label is clicked upon.

HOWEVER, this is a very cumbersome way of doing things to my mind. In addition, sizing of the controls in each day's bar is far more difficult than I expected.

Anyone got any ideas as to how I might achive this task in a more polished way?

View 5 Replies View Related

Here's A Challenge!!!

Jun 12, 2006

This is probably not too bad to most... Considering I'm still pretty new to this...

I'm designing a database to track training for about 6 offices where I work. I am having trouble trying to set up a questionaire that adds a worker's training requirements automatically when a new worker is added to the system.

TABLES

tblPersonnel
PersonnelID (Primary Key)

tblRequirements
RequirementID (Primary Key)
RequirementTypeID - Denotes the type of requirement based off of the question on the questionaire.

tblPer_Req (Join Table)
PersonnelID (Joined to tblPersonnel)
RequirementID (Joined to tblRequirements)

QUERY

In my query I have pulled the PersonnelID from tblPersonnel, the RequirementID from tblRequirements and the RequirementTypeID from tblRequirements.

FORM (part 1) - New Member Form

Enter the new member's information...

FORM (part 2) - Requirement Questionaire (Must bring over PersonnelID from FORM (part 1)...

I have asked the following question... (total of 10 questions, but we'll just go with one for now...)

"Does the member wear prescription eyeware?" (Yes/No - using an Option Group)
(RequirementTypeID = 2)

If the member answers yes, I want the database to automatically add the member's PersonnelID and all Requirements from tblRequirements that have a RequirementTypeID of 2 to tblPer_Req (Join Table).


So far, I have gotten the query to only work by itself as a SELECT query and was not able to add the information to tblPer_Req, let alone try to tie it together with the Option Group...

If anyone could please help me get this together I would greatly appreciate it... I've been trying to work this one through for almost two weeks, the boss has tried to help too but he's also stumpped!!!

Thanks!!!

View 3 Replies View Related

Data Challenge

Jul 14, 2006

Dear All,

I currently receive MS excel files with forty columns and 7000 rows of data. I Perform a sense check and then convert the file to csv for loading into another system.

I've had a request to start performing this task for files of up to 13 million rows. Obviouslt MS excel will not be capable of doing this.

Can anyone tell me what MS product I should use? Access, foxpro? or do I need a non MS product and if so which one?

Many thanks

Wayne

View 4 Replies View Related

A Challenge To Me But Might Be Simple To U....help Please

Apr 5, 2006

I am currently having the following difficulty. Let say i have 3 fields in a table.

Field 1: = ID
Field 2: = operation Number
Field 3: = Status
What should i do if i want the following result from a query.

Field 1:= Grouped by ID
Field 2:= Count of operation Number
Field 3:= "status1", "Status2", "status 3"(basically concatenating the strings in each group.
.:confused:

Please note that i am using Access 2003.
eg of how table wold look
Gr ID CountOf Operation Status
1299B 10 R
1299B 20 C
1299B 30 B
1299B 40 D
1299B 50 A
1299B 60 Z
1275A 10 P
1275A 20 Z

eg of an out put for my query should look like
Gr ID CountOf Operation Status
1299B 6 R,C,B,D,A,Z
1275A 2 P,Z
Please note that it is important that the order is maintained.... ie in the case of 1275A the order should be PZ and not ZP..

Apprecate any help



Thanks in advance :) :)

View 4 Replies View Related

A Real Challenge...help Me Think Outside Box Please!!

May 19, 2006

BACKGROUND

The current challenge I have in MS Access in really giving me a headache. I can’t seem to find an answer or indeed think “outside the box” so I am hoping there is someone who can help.

Below I will outline the design of my database, the problem I have and my proposed solution. My solution is “What I want to do” rather than “How I do it”. If you have the time and patience to look at my problem and suggest how I proceed, I will be really grateful.

CURRENT DATABASE DESIGN

I created a database that imports daily telephone data. The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.

I extract data from the phone system for “Lines” (3 digit code) e.g. ‘301’,’302’ which each have a corresponding line “Description” e.g. ‘New Customers’, ‘Accounts Queries’. There are three main daily extracts (1) Inbound Calls (2) Outbound Calls and (3) Time. Each extract has either “Line” or “Description” as the unique identifier but not both.

There are 50 lines with matching descriptions and each is allocated to one of 20 teams. I have a query which links all three extracts, groups the data by team and date. Therefore this query creates 20 records (teams) for each day (date).

CURRENT QUERY AND TABLES

Tbl Line Lookup (50 records)
Line
Description
Team

Tbl Inbound Calls
Line
Date
Offered Calls
Answered Calls
Abandoned Calls

Tbl Outbound Calls
Description
Date
Outgoing Calls

Tbl Time
Description
Date
Answer Time
Abandon Time
Talk Time
Wrap Up Time

Qry Grouped By Team Daily

Line – linked to line in Tbl Incoming Calls
Description – linked to description in both Tbl Outgoing Calls and Tbl Time

Team – Grouped field
Date – Grouped filed
Offered Calls - Sum
Answered Calls - Sum
Abandoned Calls - Sum
Outgoing Calls - Sum
Answer Time - Sum
Abandon Time - Sum
Talk Time - Sum
Wrap Up Time - Sum

PROBLEM

Customer Services “Team” is made up of six lines (301-306) and Business Partners “Team” is made up of three lines (307-309). For each day my query creates a record for each of these teams based on how the line is allocated in Tbl Line Lookup.

The business had decided that line 304 calls are part of the Business Partners with effect from 01/05/06. If I change the team name associated with 304 in Tbl Line Lookup this will, incorrectly, allocate all information (from 01/01/06 to present) to Business Partners.

I need a mechanism to allocate 304 to Customer Services prior to 30/04/06 and to Business Partners after 01/05/06.

PROPOSED SOLUTION

I want to introduce a new field in Tbl Line Lookup called Effective Date.

Tbl Line Lookup (50 records)
Line
Description
Team
Effective Date

Then I want to introduce a new query which links the data in the three data tables and allocates the correct team name by comparing the date in these extracts with the effective date in the Tbl Line Lookup.

I would then run my original query (Qry Grouped By Team Daily) over this query instead of the original tables.

Please can someone tell me if it is possible to look up values in another table to determine the value of a field in the way I have described?

View 5 Replies View Related

Access Challenge!

May 22, 2006

The scenario - I use MS Access to access an advantage database system from a program we use. I can either import or link to each table in the program's database via OBDC drivers.

The problem - I need to track the status changes of our customers. Tracking customer status changes are not recorded in the advantage db. The TRDATA table contains the information. From now on, I want my access db to track the changes and save it in access for report purposes. The status's available are A, B, I, K, L, O, P, W, X and Y.

The solution - So far, I have queried the TRDATA table to pull the Account #, Buyer, and status. I made a crosstab query which would kind of aggregate the data into a readable form. If the account is active, there would now be a 1 in the column A. I then made another query which would then update a table. From here, I'm unsure of what to do. Obviously, there needs to be a comparison of some sort. If only account 1234 changes from an A to a Y, then I want a new table to show all the other accounts where they stand and a change of Y for the status with the date it changed. I hope I'm explaining myself well enough!! I’m very unsure of where to go from here.

View 2 Replies View Related

Count Challenge

May 24, 2007

I have a problem!

I have a table, tbl_Members.

tbl_Members contains many fields, two being SName and RefferedBy.

ReferredBy is stored in the table from a lookup from a form that looks up the SName field from tbl_Members.

What i want to do is create a query from this table with an extra field which counts the number of instances based on the records SName, in a field called NoReffers.

For example, i want a query that looks like:

qry_tbl_Members:

-----SName-----ReferredBy-----NoReffers---
-----Bloggs--------Hills-------------1-------
------Hills--------Barnard-----------2-------
-----Smith--------Bloggs----------- 0------
----Truman--------Hills-------------1------
---Williamson-----Truman-----------0------

I 've been looking into the Count() function, but cant seem to work it out!

Can someone help me?

View 10 Replies View Related

Dcount Challenge

Jun 30, 2006

Access 2000

Hi All

I am running a dcount
I need to count values that are between (and including) 3000 and 3499 the field is cmbLkpSCType
I also need to count values that are between (and including) 3500 and 3999
The cmbLkpSCType values are 3000 through 3499 the field is cmbLkpType

My challenge is to find a way of counting one and not include the other

If Me.cmbLkpSCType = "3000" Then
strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2))
strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')")
End If
'*****************
If Me.cmbLkpSCType = "3500" Then
strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2))
strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')")
End If

TIA
Detrie

View 3 Replies View Related

Listbox Challenge

Jul 18, 2006

Hi folks!

I have a listbox in one of my forms with the following RowSource:

SELECT fsVisbetalingsprocent.navn1, fsVisbetalingsprocent.betalingsprocent1,
fsVisbetalingsprocent.fradato1 FROM fsVisbetalingsprocent WHERE
fsVisbetalingsprocent.sælgernr1=forms.vissælger.sæ lgernr ORDER BY
fsVisbetalingsprocent.fradato1;

The output looks like this:

(navn1) (betalingsprocent1) (fradato1 is hidden)
June 0,50
August 0,30
August 0,30
August 0,30

In my query 'fsVisbetalingsprocent' the 'betalingsprocent' is allways the same when the month is the same. So I want the listbox to write the month (navn1) only once...

I've been trying hard for an hour or so, and I just can't get it to work!

Any suggestions???

// JR

View 2 Replies View Related

Data Import Challenge

Jan 20, 2006

I created a database to assist in analyzing the contents of an Excel spreadsheet (provided by a third-party contract). The format of the data in the spreadsheet is denormalized to look something like the following:

Parent1ID Parent1Name Child1ID Child1Name
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name

Parent2ID Parent2Name Child1ID Child1Name
Parent2ID Parent2Name Child4ID Child4Name

It's important to note that the ChildIDs are NOT unique.

This was not a problem initially because I was able to import the entire worksheet into a single table and then run queries to populate the Parent and Child tables.

HOWEVER, they are now introducing "versioning" in a way that is causing a problem for me. When a new version is being created, they repeat the Parent and ALL of the Child rows, but only put the versioning information on the FIRST Child in the group. (They refuse to repeat the versioning information on all of the rows). So the data now looks like the following:

Parent1ID Parent1Name Child1ID Child1Name EffectiveDate1
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name

Parent1ID Parent1Name Child1ID Child1Name EffectiveDate2
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name
Parent1ID Parent1Name Child5ID Child5Name

Parent2ID Parent2Name Child1ID Child1Name EffectiveDate1
Parent2ID Parent2Name Child4ID Child4Name

I MUST relate all of the Child rows for a particular version to the correct version of the Parent, but have not been able to come up with any idea of how to do so.

Does anyone have suggestions on how to approach this problem?

View 6 Replies View Related

Challenge: Real Time Queries And Lookup

Mar 4, 2006

Greetings!

Hoping somone could help me here.

I am trying to create a database (duh!). At the moment, I have two tables. One table is a small timetable with the fields:

Day, Airline, Flight Number, Time, Origin, Destination, Aircraft

The other table (Delay) has the following fields:

Date, Day, Airline, Flight Number, Time, Origin, Destination, Delayed by.

I would like to do the following. In Delay I put in the date: 01/07/2005, fine. The Day (field format Date/Time dddd) is also 01/07/2005 which Access transofrms into the word "Friday". Airilne is BA. In Flight Numbers, I want the database to do a query on "TIMETABLE" to search all flights that operate on a Friday as some do not operate daily. (By the way in the 'Day' column of Timetable the values are "Monday Tuesday Wednesday Thursday Friday Saturday Sunday" or "Monday Thursday Saturday" depending on the flight). This query/lookup should be performed right after I type 01/07/2005 in the "day" field of "Table: Delay". A lookup combo box should be displayed when I arrive at the field FLight Number. I select a flight (e.g.: BA001) and automatically the Time, Origin and Destination of the flight should appear in the "Table: Delay".

I have been unsuccessful at even getting past the first hurdle. I tried a query of the "Table: Timetable" and fields "Day" and "Flight Number". The Criteria I do not know what to put in. "Like [Delay]![Day]" or "[Delay]![Day]" does not help and brings up nothing. However, "Like *Monday*" does bring up all the flights which operate on a Monday. However isn't there some way of telling the query to automatically occur after I type in the relevant day in the "Delay" table? Futhermore I would like to avoid (but not totally exclude if it's impossible) macros as eventually I hope to publish this to SQL or whatever.

Cheers in advance.

View 1 Replies View Related

Programming Challenge/minor Brain Movement Anyone?

Mar 31, 2005

Hi,

Ok. So, here's the task

There are multiple employees on a table, each with a period of time for which they worked. I am trying to gather these times together to see how many complete records I have for the time period of 4/1/1999 to 12/31/2004. So, for example,

Employee 1 works from 4/1/1999 to 12/31/2000.

Employee 2 works from 1/1/2000 to 12/31/2004.

(From these two records, there will be 1 complete record of 4/1/99 to 12/31/04 AND 1 remaining record for the year 2000 left over.)

Employee 3 works from 1/1/2000 to 12/31/2004.

(From these three records, 1 complete record + 2 2000 + 1 each for the years 2000-2004.)

Employee 4 works from 1/1/2001 to 12/31/2003.

(= 1 complete + 2 2000 + 2 2001-2003 + 1 2004)

Employee 5 works from 4/1/1999 to 12/31/1999

(= 2 complete + 1 2000 + 1 2001-2003)

And this goes on for all the employees. I need to find out the end amount of how many complete records and remaining dates all the employees have worked. I hope this was at least, somewhat clear. I remember doing something similar to this ages ago in programming class, but since then, my mind has degraded. =P Any help, insights, or advice would be greatly appreciated on how to get this done. =)


G

View 5 Replies View Related

Major Challenge - Update A Table Based On Calculation

Nov 14, 2004

I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!

Problem:

Where I work we have a series of machines… named “ C1, H7, H8 etc”

Each line has 3 counters on it,

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.

Sounds straight forward enough….but the process gets a little more complicated.

Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters

Grouped lines are given a group code – P8 in this case.

Example…

C1 not joined

H7 and H8 joined.

C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons

H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons

H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons

P8
Output – 1600 Cartons

The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.

As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.

So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.

I now want to implement the following calculation to my DB,

(see Attached Spreadsheet)

the calculations a re stored in cells b12 & b13 in the spreadsheet.

so that the output figure is separated

in proportion to impressions made.

I then need the figure per line to be fed back to the “tblProduction” output field..

in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...

Can this be done automatically, if so please help me…

Andy

View 2 Replies View Related

Major Challenge - Update A Table Based On Calculation

Nov 14, 2004

I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!

Problem:

Where I work we have a series of machines… named “ C1, H7, H8 etc”

Each line has 3 counters on it,

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.

Sounds straight forward enough….but the process gets a little more complicated.

Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters

Grouped lines are given a group code – P8 in this case.

Example…

C1 not joined

H7 and H8 joined.

C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons

H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons

H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons

P8
Output – 1600 Cartons

The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.

As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.

So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.

I now want to implement the following calculation to my DB,

(see Attached Spreadsheet)

the calculations a re stored in cells b12 & b13 in the spreadsheet.

so that the output figure is separated

in proportion to impressions made.

I then need the figure per line to be fed back to the “tblProduction” output field..

in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...

Can this be done automatically, if so please help me…

Andy

View 5 Replies View Related

Query In Forms Question (printing A Single Selected Query Record From A Form?)

Oct 19, 2004

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View 1 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Opening Design View Of Append Query Without Timing Out Source Query

Mar 4, 2015

In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.

All is well and fine until source data changes and the pass-through query runs too long and times out.

If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table

Is there a way to open an Append or Make-table query in Design view without invoking the source query?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved