My First Database

[McQwin]

I'm having complications with this database I've been given. One table has 10,000+ lines, the most important of which is a unique requisition number. This requisition number will appear several times, each with a different status and name attached to it. Once the requisition passes a certain point, I can bill accordingly.

What I would like to do is find the status at a certain point, and put it on a list to bill a client. Once it’s on a list, I create another table using those requisitions with those numbers that I've already billed. The plan was that all of the requisition numbers that are in the main table will be filtered using the second table. What ends up happening is (I think), since there are multiple lines, only one(or a few) will actually get filtered.

I’ve tried, unsuccessfully, to use an update, append, even delete queries. I’m not sure if my expertise is lacking, or if any of those solutions will actually work.



Ultimately, what I would like is once I put those numbers on the new table to not have them show up anymore in queries

What the most challenging part about this, is that the database is updated everyday. If (theoretically) we solve the problem, the next day, a new line item may come in and throw the system off again...


Any help would be much appreciated

[June7]

I gather that the requisition number is not unique in this table - there will be multiple records for each req number, each differentiated with a status value. Each status can appear only once for each req number? The name seems irrelevent. Not understanding reason to create another table. Why can't queries handle this with appropriate filtering criteria?

[McQwin]

The requisition number is unique to our company and our client. Each time there is activity on that number is what generates a new line in the table. The pertinent data is that name and the data associated with it.

I feel like there should be a way to append the req number with the values I want…

[June7]

Didn't quite respond to my questions/assumptions.

The req number is not a unique value in the table?

Each status value will appear only once for each req number?

Name is what - customer, employee?

Still not understanding why queries can't accomplish the task without having to save data to another table.

Can you attach a file of sample data - Excel, Access, Word?

[McQwin]

I am sorry for not responding properly. I would like to upload a sample of the data that makes up the table, but it doesn't seem to let me upload an excel file. Since I'm dealing with company sensitive data, I'm wary to put it online.

The req number is not a unique value in the table?
It is not unique in the table no. This is why we're having such troubles with our database. The req number will show up on the table several times, each time it shows up a different candidate, and different req status.

Each status value will appear only once for each req number?
Since each req number can have multiple candidates on it, the number may show up several times, with the same status value-- but with different candidates.

Name is what - customer, employee?
Name is for a candidate that gets placed into the req number.

Still not understanding why queries can't accomplish the task without having to save data to another table.
What we were hoping to accomplish with the new table is a way for the database to filter out the req numbers that have already been billed. But since each number has several different candidates, each with a different filing status, it doesn't seem to be accomplished.

I appreciate all of your help, thank you again.

[June7]

Import relevant tables to new db, remove confidential data, dummy the data, reduce number of records, Compact & Repair, attach to post. Zip it if needed, zip up to 2mb can be attached. Excel can be zipped.

[McQwin]

I'd like to point out a few things:

the Billing hires qurey is that query that should filter the req numbers that have already been billed. However, when you run it several req numbers appear that I've already billed. Moreover, they will appear several times in the query.

In the Design view, I'm hoping to look at all those req's that are at a certain point or above. There is a hirearchy which may or may not be useful:
Offer Accepted < Ready to Hire < Hired.
A req may "skip" one of these, so they're all needed to some degree.

In the last two columns, I'm looking for those reqs that have been billed Fee 1, but not Fee 2

I don't know if there is a way to do this, but the goal is to have a place that shows only those reqs once.

I thank you again for all the help you've put into this

[June7]

There is so much weird about All Data table. Field called Date Open is a number instead of date datatype, Target Salary is a text instead of number datatype, some numbers with commas. Records are very repetitive, often only difference is File Date and ID. Sometimes Manager Name has number suffix.

If you don't care about showing the Current HR status value, try this:
SELECT [All Data].[Req Number], [All Data].[Candidate Last Name], [Billed Req's].[Fee 1], [Billed Req's].[Fee 2]
FROM [All Data] LEFT JOIN [Billed Req's] ON [All Data].[Req Number] = [Billed Req's].[Req Number]
GROUP BY [All Data].[Req Number], IIf([Current HR status] Like "Hired*" Or [Current HR status] Like "*Accepted*",True,False), [All Data].[Candidate Last Name], [Billed Req's].[Fee 1], [Billed Req's].[Fee 2]
HAVING (((IIf([Current HR status] Like "Hired*" Or [Current HR status] Like "*Accepted*",True,False))=True) AND (([Billed Req's].[Fee 1]) Is Not Null) AND (([Billed Req's].[Fee 2]) Is Null))
ORDER BY [All Data].[Req Number];

Still not quite understanding the data and how you determine when to pay a requisition so can't offer suggestion on how to get around using Billed Req's table.

BTW, advise not using spaces, special characters, punctuation (underscore is exception) in names. If do must enclose in []. Sometimes Access will provide the [] but don't always count on it, and VBA won't supply the [].